Merge and Combine Cells in Excel without loosing Data using VBA

Usually, we merge cells in Excel to create a header or a label on the top of our worksheet or a table of data. Merging, often involves multiple columns, where we combine the data into one single cell. Now, what happens when we have data in rows, which we want to merge and combine into one cell? I’ll share with you a simple VBA macro (program) to merge and combine cells without losing data.

Merge and Combine Cells in Excel without losing Data using VBA

The image above reflects the output of the example that I have shared below. I have a row of data. Each cell has a value and I wish to merge all text and number values and show it as a single sentence in a cell on the top of my worksheet. You will not lose any data in the process.

The Macro

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()
    mergeCellData
End Sub

Sub mergeCellData()
On Error GoTo ErrorHandler

    Dim cell As Range
    Dim mySelRange As Range
    Set mySelRange = Selection
    
    Dim mergeText As String
    
    ' LOOP THROUGH SELECTION OBJECT AND
    ' EXTRACT ALL CELL VALUES AND STORE IT IN A VARIABLE.
    For Each cell In mySelRange
        mergeText = mergeText & cell.Value & " "
    Next cell
    
    ' NOW, MERGE AND COMBINE THE EXTRACTED TEXTS (NUMBERS AS WELL) AND
    ' ADD IT TO THE FIRST CELL.
    With mySelRange
        .Clear
        .Cells(1).Value = mergeText
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .Merge
        .WrapText = True
    End With
ErrorHandler:
    '
End Sub

Now here's how you will merge all the cells value. First, select or highlight the rows that you wish to merge and then hit the button. See the image.

Highlight cells to Merge Cells using VBA

In the macro, I am using the Selection object as a Range. The Selection object represents the selected area in the worksheet.

Here’s more on VBA Selection object.

I’ll loop through the Selection object to extract each cells 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

If you are not comfortable with VBA programming or macros, then you can use Excel’s Justify feature.

Inside the Home tab (in your Excel worksheet) look for the Fill option. It’s a dropdown. See the image.

Excel's Justify Option to Merge Cells

However, 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.

Limitation

The Excel Justify feature however, comes with some limitations. It does not work on cells with numbers and formulas.

While explaining about merging using VBA macro in the first example above, I have added a number (in the 6th row). While the VBA code works fine, and will merge the numbers along with texts, applying the Justify feature will throw an error (in case a cell has numbers or formulas).

Excel Justify and Merge Error with Numbers

Therefore, 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).

← PreviousNext →