Excel VBA – Merge Cells – Unmerge Range

In Excel macro, using the range or selection object, merge cells & unmerge operations can be done.

Nothing much to explain in this.

But just remember that: once the cells are merged, the cell can be reference with the first address.

For example: If you merge cells A1:B1 , then you can assign a value to the merged cell by using Range (“A1”)  object.

Excel VBA to Merge Cells

Use this VBA code to Merge cells right from your macro code. This is not a difficult to code to make.

You can also use Record macro option to get this code snippet.

Sub Merge_Cells()
    'Code to Merge Excel Cells
    Range("G5:H5").Merge
End Sub

Sub Merge_Range(iRng As Range)
    iRng.Merge
End Sub

Once you press F5, the cells within the range will be merged as one cell.

The same can be used as below.

Unmerge cells in Excel VBA

Just use the keyword unmerge with Range object.

Sub unMerge_Cells()
    'Code to Merge Excel Cells
    Range("G5:H5").UnMerge
End Sub

Sub unMerge_Range(iRng As Range)
    iRng.UnMerge
End Sub

Keep the cursor inside the first function and press F5. Excel will unmerge the range given as input.

Using Selection Object to Merge & Unmerge

Instead of using the Range object, if we are not sure on which cells need to be merged, but just want to merge all cells in selection.

Sub Sel_Merge_Cells()
    'Code to Merge Excel Cells
    Range("G5:H5").Select
    Selection.merge
End Sub
Sub Sel_unMerge_Cells()
    'Code to Merge Excel Cells
    Selection.UnMerge
End Sub

In the code, to illustrate which cells are selected, we have explicitly mentioned the range.selection.

In your case, if the cells are already selected, then just use ‘Selection.merge’.

Leave a Reply