Merge and Unmerge Cells using VBA in Excel

In this tutorial, we would learn about different ways to merge cells using VBA. We would also write a Sub Procedure that merges the cells with no loss of data in the cells that are not the upper left cell of the merged range.

So let us begin learning.

Merge Cells using VBA

We can use the Range object to specify the range of cells that we wish to merge and then use the Merge method of the Range object to merge the range. For instance, when you run this Sub Procedure, it merges the range A1:D1 of the active sheet.

Sub merge_cells()
'merge the range
Range("A1:D1").Merge
End Sub

This is the result.

Unmerge Cells using VBA

Use any of the cells of the merged range of cells to unmerge the entire range of cells. We have merged the range A1:D1. We can unmerge the range using any cell A1, B1, C1, or D1.

Sub unmerge_cells()
'you can also use B1,C1 or D1 to unmerge cells
Range("A1").UnMerge
End Sub

When you run this Sub Procedure in which the range A1:D1 is merged, it would be unmerged as a result.

Merge Rows using VBA

We can merge the complete adjacent rows with the help of the Range object followed by the merge method. We need to specify the range as <First_Row>:<Last_Row>

For instance, if we wish to merge the four rows, from row 2 to row 5, we can use this procedure.

Sub merge_rows()
'merge the rows
Range("2:5").Merge
End Sub

Merge Columns using VBA

We can merge the entire adjacent columns by specifying the starting column at the left and the last column to the right of the merged column range.

Let us say we need to merge columns C, D, and E. So we would use this procedure.

Sub merge_cols()
Range("C:E").Merge
End Sub

Merge and Center the Contents Horizontally

Let us suppose we have the following range of cells A1:E1 and cell A1 contains a text string “ExcelUnocked”.

We want this to center align when we merge the range. Use this procedure to first merge and then center align the range.

Sub mergeHorizontalCenterAlign()
'merge and align center horizontally
Range("A1:E1").Merge
Range("A1:E1").HorizontalAlignment = xlCenter
End Sub

Merge and Center the Contents Vertically

We can first merge the cells and then center the contents vertically. Let us say we have a range A1:A4 and A1 contains the text string “ExcelUnlocked”.

This Sub Procedure would merge the range A1:A4 and the content in cell A1 would be vertically aligned in the center of the merged range.

Sub mergeVerticalCenterAlign()
'merge and align center vertically
Range("A1:A4").Merge
Range("A1:E1").VerticalAlignment = xlCenter
End Sub

Merge Cells Across a Range

We can merge the cells across a range. All we need to do is, set the across parameter of the merge method to True.

Let us say we want to merge across the range A1:D4. Use the following procedure for this.

Sub merge_across()
'merge across a range of cells
Range("A1:D4").Merge Across:=True
End Sub

Merge Cells of Specific Worksheet

By now we have been merging the cells of the active worksheet. We can also merge the cells of any particular worksheet with the help of the Worksheets Collection.

Let us say you wish to merge the range in the worksheet named Sheet2. Use this code.

Sub merge_sheet2()
'merge cells of a worksheet Sheet2
Worksheets("Sheet2").Range("A1:A5").Merge
End Sub

As a result, this merges the range A1:A5 in Sheet2 without actually opening it.

Merging and Combining the Cell Contents

When we merge the cells that contain cell values, all the cell values are lost only except the upper left cell of the unmerged range which becomes the cell value for the merged range.

The contents of cell A2 and A3 is lost. We can define a macro that would combine the cell contents and then merge the cells. The merged cell would display the combined content of all the merged cells.

Use this Sub Procedure.

Sub merge_combineCellContents()
Dim combined As String
Dim rng As Range

Set rng = Range("A1:A3")
For Each Cell In rng
    combined = combined & " " & Cell
Next Cell

With rng
    .Merge
    .Value = Trim(combined)
    .WrapText = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End Sub

As a result, when you run this Sub Procedure, it combines the content of range A1:A3, merges them, and then assigns the combined content to the merged cell. It would wrap the text and center it horizontally and vertically.

This brings us to end.

Thank you for reading.