Last updated: 12th February 2025
Typically, we merge cells in Excel to create headers or labels at the top of our worksheets or data tables. This process often involves multiple columns where data is combined into a single cell. But what if we have data in rows that we want to merge and combine into one cell? In this article, I'll share a simple VBA macro to merge and combine cells in Excel without losing any data.The image above shows the result of the example provided below. I have a row of data where each cell contains a value. My goal is to merge all text and numerical values into a single sentence and display it in a cell at the top of my worksheet. This process ensures that no data is lost.
The program or the macro will execute when you click a button. Therefore, I’ll write the code in the button’s click event, in Sheet1. The button is an ActiveX control.
Option Explicit Private Sub CommandButton1_Click() Call MergeCellData End Sub Sub MergeCellData() On Error GoTo ErrorHandler Dim cell As Range Dim mySelRange As Range Dim mergeText As String Set mySelRange = Selection mergeText = "" ' Loop through selection and store cell values in a variable. For Each cell In mySelRange mergeText = mergeText & cell.Value & " " Next cell ' Merge and combine the extracted texts (including numbers) and add it to the first cell. With mySelRange .Clear .Cells(1).Value = Trim(mergeText) ' Remove any trailing spaces. .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .WrapText = True End With Exit Sub ErrorHandler: MsgBox "An error occurred: " & Err.Description, vbExclamation, "Error" End Sub
How it works?
First, select or highlight the rows that you wish to merge and then hit the button. See the image.
In the macro, I am using the Selection object as a Range. The Selection object represents the selected area in the worksheet.
Learn more about VBA Selection object.
I’ll loop through the Selection object to extract each cell's value and concatenate the texts and numbers (if any) and store it in a variable.
Finally, I’ll use the Selection object again, to assign the extracted values to a cell (Cells(1) or the first cell in the first row), align it according to my requirement and merge it. I have set WrapText as True.
Merge Cells using Excel’s Justify Feature
Here's an alternative method. If you are not comfortable with VBA programming or macros, you can use Excel’s Justify feature. Scroll down to read its limitations.
To find this option, go to the Home tab in your Excel worksheet and locate the Fill dropdown menu. Refer to the image for guidance.
Microsoft 365
Excel 2007
➡️ Before using the option, you’ll need to first increase the width of the column. Else, it will not give you the desired result.
Remember, the column should be wide enough to write or merge all the texts in one cell.
The Excel Justify feature however, comes with some limitations. It does not work on cells with numbers and formulas.
🚀 Here's a workaround. Get rid of the number (or convert the number to text by adding a single quote ' to merge the cells into one. I personally prefer the first example (using VBA code).