Merge and Combine Cells in Excel Without Losing Data: A Step-by-Step VBA Guide

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.

Merge and Combine Cells in Excel without losing Data using VBA

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 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()
    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.

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.

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's Justify Option to Merge Cells in Microsoft 365

Excel 2007

Excel's Justify Option to Merge Cells

➡️ 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.

Excel Justify and Merge Error with Numbers

🚀 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).

← PreviousNext →