VBA Merge Cells and Unmerge cells in Excel – VBA Tutorials

Introduction/Problem Statement

In this blog, we will talk about different ways of VBA merge cells or unmerge cells using VBA. In most of cases user do it manually because “Merge” & “Unmerge” buttons shows in the “Home” tab of the ribbon but sometimes user writes a program where they need to VBA merge cells and unmerge cells, in this case herein below we have shown some examples of the code which will make the job easier of the users.

Solution

VBA Merge cells:

This code will merge cells B1:B4.

Merge Cells

Sub KDataScience_MergeCells()
    
    Worksheets("Sheet1").Range("B1:D1").Merge

End Sub

Merge Row

We can merge Entire rows by specifying the row number that we want to merge. This code will merge rows 2 to 6.

Merge Row

Sub KdataScience_MergeRows()

    Worksheets("Sheet1").Range("2:6").Merge
    
End Sub

Merge Columns

We can merge entire columns by specifying the columns letters of the that we want to merge. This code will merge columns A:D

Merge Columns

Sub KDataScience_MergeColumns()

    Worksheets("sheet1").Range("A:D").Merge

End Sub

Merge and Center Cell Contents Horizontally

This code will merge the range B1:B4 and set the alignment of contents cell to center.

Merge and Center Cell Contents Horizontally

Sub KDataScience_MergeandCenterContents()
    'To merge cells
    Worksheets("sheet1").Range("B1:D1").Merge
    'To set cell allignment to left
    Worksheets("sheet1").Range("B1:D1").HorizontalAlignment = xlCenter
 
End Sub

Merge and Center Cell Contents Vertically

This code will merge the range A1:A4 and set the alignment of contents cell to center.

Merge and Center Cell Contents Vertically

Sub KDataScience_MergeandCenterContentsVertically()
 
    'To merge cells
    Worksheets("sheet1").Range("A1:A4").Merge
    'To set alignment to center
    Worksheets("sheet1").Range("A1:A4").VerticalAlignment = xlCenter
 
End Sub

Unmerge Cells

We can unmerge cells by using the unmerge method of range. The specified range must be the part of merge range

This code will unmerge all cells between the range of H1 to H30(H1 To H30 is the part of merged cells).so we will define any range between H1 To H30 in the range object of the VBA code(as we have mention H21 in our code).

Unmerge Cells

Sub KDataScience_UnmergeCells()
 
    Worksheets("sheet1").Range("H21").UnMerge
 
End Sub

Unmerge all merge cells in Activesheet

If the active sheet has multiple merge range, then we can use this code

Unmerge all merge cells in Activesheet

Sub KDataScience_Unmerge_AllCells()

ActiveSheet.Cells.UnMerge

End Sub

Conclusion

If you want to VBA merge cells or unmerge cells,  use any code shown above (as per user need).

Leave a Reply

Your email address will not be published. Required fields are marked *