VBA to Merge Cells in Excel (9 Methods)

Get FREE Advanced Excel Exercises with Solutions!

In Excel, to merge multiple cells or ranges and make it as one singular cell, Range.Merge method is used. In this article, we will show you how to merge multiple cells in Excel with VBA.


Range.Merge Method in Excel

  • Purpose:

Create merged cells from multiple specified cells.

  • Syntax:

Range(your_range).Merge(Across)

  • Parameter:
Parameter Required/ Optional Data Type Description
Across Optional Variant

The default value is False. When you want to merge cells in each row of the specified range as separated merged cells, set the value as True.

  • Return Value:

Merged cell


VBA to Merge Cells in Excel: 9 Methods

In the following section, you will learn 9 different methods to merge cells in Excel with VBA macro.

1. Embed VBA to Merge Cells in Excel

If you want to merge specific cells in Excel with VBA – like we will merge the cells B5 and C5 from the given dataset below – follow the steps given.

Steps:

  • Press Alt + F11 on your keyboard or go to the tab Developer -> 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.

VBA to Merge Cells in Excel

  • Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

You will see the cells B5 and C5 have now merged and become one single cell.

Result of VBA to Merge Cells in Excel


2. Implement Macro to Combine Rows in Excel

Here you will see how to combine rows in Excel with VBA macro. We will merge rows 7 to 9 with VBA from the example given.

Steps to merge rows with VBA are shown below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
Sub Merge_Rows()
    Range("7:9").Merge
 End Sub

Your code is now ready to run.

VBA to Merge Rows in Excel

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

Result of VBA to Merge Rows in Excel


3. VBA Macro to Merge Columns in Excel

In the previous section you saw how to merge rows; in this section, you will learn how to merge columns with VBA in Excel. We will merge columns B and C from the shown dataset below.

Steps to merge columns in Excel with VBA are shown below.

Steps:

  • Similarly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
Sub Merge_Columns()
    Range("B:C").Merge
End Sub

Your code is now ready to run.

VBA to Merge Columns in Excel

  • Run the code and the columns that you wanted to merge will be merged after the code execution.

Result of VBA to Merge Columns in Excel


4. Insert VBA Macro to Merge Cells Based on Cell Value

You can also merge cells based on the cell value that you specified. From the following dataset, we will merge the cells onward from the cell that contains “Alaister Cook”.

Steps to merge cells based on cell value with VBA are given below.

Steps:

  • Open Visual Basic Editor from the Developer tab 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.

VBA Macro to Merge Cells Based on Cell Value in Excel

Here,

  • FirstRow = 5 -> Our dataset started from row 5.
  • iColumn = 2 -> Column B, the column that holds the value in our case.
  • FirstColumn = 2 -> Column B, first column in our dataset.
  • LastColumn = 5 -> We will merge till column E.
  • iValue = “Alaister Cook” -> Set the cell value.
  • Set iWorksheet = Worksheets(“Cell Value”) -> Worksheet name for our dataset.
  • Run this code and notice that the cell that stored “Alaister Cook” is now merged till column E.

result of VBA Macro to Merge Cells Based on Cell Value in Excel


5. Merge Both Text and Cells with VBA in Excel

Till now we are merging only cells in Excel. But what if you want to merge not only the cells but also the texts inside them.

In our case, we will merge all the texts from cell B5 to B12 and then merge the cells also.

Steps to merge both texts and cells with VBA macro are given below.

Steps:

  • Open Visual Basic Editor from the Developer tab 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.

Merge Both Text and Cells with VBA in Excel

  • Run this piece of code and look at the result shown below. Not only the cells from B5 to B12 are now merged but the texts inside them are merged as well.

Result of Merge Both Text and Cells with VBA in Excel


6. VBA to Combine Cells Horizontally and Align Cell Contents in the Center

You can combine cells and align them horizontally while placing the cell contents in the center of the cell with the VBA code and we will show you how to do that in this section.

From the above dataset, we will merge the cell from B8 to E8 horizontally and align the cell contentAlaister Cook” – in the center.

The steps to do that are discussed below.

Steps:

  • Open Visual Basic Editor from the Developer tab 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.

VBA to merge Cells Horizontally and Align Center in Excel

  • Run the code and notice that the cells that we provided inside the code are now merged horizontally and the contents of the cell, “Alaister Cook”, are now centre-aligned.

result of VBA to merge Cells Horizontally and Align Center in Excel


7. VBA Macro to Combine Cells Vertically and Align Cell Contents in the Center

Just like you can combine cells horizontally, you can combine them vertically as well.

Let’s see how to combine the cells from B5 to B8 vertically from our dataset and place the contents – “Joe Root” – in the center.

Steps:

  • Open Visual Basic Editor from the Developer tab 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.

VBA to merge Cells vertically and Align Center in Excel

  • Run the code and notice that the cells that we provided inside the code are now merged vertically and the contents of the cell, “Joe Root”, are now center-aligned.

Result of VBA to merge Cells vertically and Align Center in Excel


8. Implement VBA to Merge Cells Across the Range in Excel

When you want to merge cells in each row of the specified range as separated merged cells, you can do that with VBA code with the Across parameter in the Range.Merge method.

Let’s see the code of merging Cell B7 in the same row through E7 in Excel.

Steps:

  • Open Visual Basic Editor from the Developer tab 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.

VBA to Merge Cells Across the Range in Excel

  • Run this piece of code and see in the picture below that the B7 cell has merged in the same row through cell E7.

Result of VBA to Merge Cells Across the Range in Excel


9. Embed VBA to Merge and Center Every Two Cells in a Range

In this part, you will learn how to merge and center every two cells in a range in Excel with VBA macro.

The steps to do that are given below.

Steps:

  • Open Visual Basic Editor from the Developer tab 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.

VBA to Merge and Center Every Two Cells in a Range in Excel

  • Run the macro and you will get the dataset where every two cells are merged and centered.

Result of VBA to Merge and Center Every Two Cells in a Range in Excel


Download Workbook

You can download the free practice Excel workbook from here.


Conclusion

This article showed you how to merge cells in Excel using VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


<< Go Back to Cells | Merge | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo