VBA to Merge Cells in Excel (9 Methods)

In Excel, to merge multiple cells or ranges into one singular cell using VBA, the Range.Merge method is used.

Range.Merge Method in Excel

• Purpose:

Create merged cells from multiple specified cells.

• Syntax:

Range(your_range).Merge(Across)

• Parameter:

• Return Value:

Merged cell

Method 1 – Use VBA to Merge Cells

Let’s merge cells B5 and C5 from the dataset below.

Steps:

• Press Alt + F11 on your keyboard or go to the Developer tab -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Copy the following code and paste it into the code window:

Sub MergeCells()
    Range("B5:C5").Merge
End Sub

Your code is now ready to run.

• To run the macro, either press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm, or just click on the small Play icon in the sub-menu bar.

Cells B5 and C5 have now merged and become one single cell.

Method 2 – Use VBA to Combine Rows

Let’s now combine rows in Excel with a VBA macro. We will merge rows 7 to 9 from the example below.

Steps:

• Same way as above, open Visual Basic Editor and Insert a Module in the code window.
• Copy the following code and paste it in the code window:

Sub Merge_Rows()
    Range("7:9").Merge
 End Sub

Your code is now ready to run.

• In the same way as before, Run the code and you will get rows 7, 8 and 9 as a merged row.

Method 3 – Use VBA to Merge Columns

Now let’s use VBA to merge columns. We will merge columns B and C from the dataset below.

Steps:

• As above, open Visual Basic Editor and Insert a Module in the code window.
• Copy the following code and paste it in the code window:

Sub Merge_Columns()
    Range("B:C").Merge
End Sub

Your code is now ready to run.

• Run the code and the specified columns will be merged.

Method 4 – Use VBA to Merge Cells Based on Cell Value

Let’s merge the cells after the cell that contains “Alaister Cook”.

Steps:

• Open Visual Basic Editor and Insert a Module in the code window.
• In the code window, copy the following code and paste it:

Sub MergeCellsBasedOnValue()
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim iColumn As Long
    Dim FirstColumn As Long
    Dim LastColumn As Long
    Dim iWorksheet As Worksheet
    Dim iValue As String
    Dim iCounter As Long
    FirstRow = 5
    iColumn = 2
    FirstColumn = 2
    LastColumn = 5
    iValue = "Alaister Cook"
    Set iWorksheet = Worksheets("Cell Value")
    With iWorksheet
        LastRow = .Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For iCounter = LastRow To FirstRow Step -1
            If .Cells(iCounter, iColumn).Value = iValue Then _
            .Range(.Cells(iCounter, FirstColumn), .Cells(iCounter, LastColumn)).Merge
        Next iCounter
    End With
End Sub

Your code is now ready to run.

Here,

• Run this code and the cell that stored “Alaister Cook” is now merged till column E.

Method 5 – Merge Both Text and Cells with VBA

In the Methods above we have only been merging cells. But what if you want to merge not only the cells but also the texts inside them?

Let’s merge all the texts from cell B5 to B12 and then merge the cells too.

Steps:

• Open Visual Basic Editor and Insert a Module in the code window.
• In the code window, copy the following code and paste it:

Sub MergeTextAndCells()
Dim iValue As String
Dim iRange As Range
Set iRange = Range("B5:B12")
For Each Cell In iRange
    iValue = iValue & " " & Cell.Value
Next Cell
With iRange
    .Merge
    .Value = Trim(iValue)
    .WrapText = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
End Sub

Your code is now ready to run.

• Run the code.

Not only are the cells from B5 to B12 now merged, but the texts inside them are merged too.

Method 6 – Use VBA to Combine Cells Horizontally and Align Cell Contents in the Center

Let’s now merge the cells from B8 to E8 in the following dataset horizontally, and center the cell contents (“Alaister Cook”).

Steps:

• Open Visual Basic Editor and Insert a Module in the code window.
• In the code window, copy the following code and paste it:

Sub MergeAndCenterHorizontally()
    Range("B8:E8").Merge
    Range("B8:E8").HorizontalAlignment = xlCenter
End Sub

Your code is now ready to run.

• Run the code.

The specified cells are now merged horizontally and the contents of the cell, “Alaister Cook”, is now center-aligned.

Method 7 – Use VBA to Combine Cells Vertically and Align Cell Contents in the Center

Let’s combine the cells in the dataset above vertically from B5 to B8, and place the contents (“Joe Root”) in the center.

Steps:

• Open Visual Basic Editor and Insert a Module in the code window.
• In the code window, copy the following code and paste it:

Sub MergeAndCenterVertically()
    Range("B5:B8").Merge
    Range("B5:B8").VerticalAlignment = xlCenter
End Sub

Your code is now ready to run.

• Run the code.

The specified cells re now merged vertically and the content of the cell, “Joe Root”, is now center-aligned.

Method 8 – Use VBA to Merge Cells Across a Range

To merge cells in each row of the specified range as separated merged cells, we’ll use the Across parameter in the Range.Merge method.

Let’s merge Cell B7 through E7 in the same row.

Steps:

• Open Visual Basic Editor and Insert a Module in the code window.
• In the code window, copy the following code and paste it:

Sub MergeCellsAcrossRange()
    Range("B7:E7").Merge Across:=True
End Sub

Your code is now ready to run.

• Run the code.
• Cell B7 has merged through cell E7 in the same row.

Method 9 – Use VBA to Merge and Center Every Two Cells in a Range

Steps:

• Open Visual Basic Editor and Insert a Module in the code window.
• In the code window, copy the following code and paste it:

Sub MergeEveryTwoCells()
Dim iRange As Range
For i = 5 To ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row Step 2
    Set iRange = Range(Cells(i, 2), Cells(i + 1, 2))
    With iRange
        .Merge
        .HorizontalAlignment = xlCenterAcrossSelection
        .VerticalAlignment = xlCenter
    End With
Next i
End Sub

Your code is now ready to run.

• Run the macro and the dataset where every two cells are merged and centered is returned.

Download Workbook

<< Go Back to Cells | Merge | Learn Excel