VBA Merge Range cells in Excel explained with Examples

VBA Merge range or cells in Excel will merge multiple cells in a excel worksheet using merge method of range object and then creates a merged cell. ‘Range.Merge’ method will merge the multiple cells or range, and then after merging it will make it as one singular cell at upper left corner of the range .

VBA Merge Range – Syntax

Here is the syntax to merge range or multiple cells in a worksheet. You can merge the multiple cells including the formats using ‘Range.Merge’ method.

Range(“YourRange”).Merge(

[Across])

Where
Across: It is Optional. When we mention Across as ‘True’ that means it will merge cells in each row of the specified range as separate merged cells. The default value is False.

VBA Merge Range – Example1

Here is the example to merge multiple cells using ‘Range.Merge’ method of range object in worksheet when across is false.

Sub Range_Merge_Across_False()
    Range("A1:D10").Merge
End Sub

The above macro or code will show you how to merge range(“A1:D10”) or multiple cells using VBA. In this example, we are merging range “A1 to D10” using ‘Range.Merge’ method of range object. Now the range(“A1:D10”) result will be a single cell named ‘A1’. In the above example across value is False. Please find the below screenshot for the same after merging the cells which is highlighted in yellow color.

VBA Merge Range – Example2

Here is the example to merge multiple cells using ‘Range.Merge’ method of range object in worksheet when across is true.

Sub Range_Merge_Across_True()
    Range("A1:D10").Merge(True)
End Sub

The above macro or code will show you how to merge range(“A1:D10”) or multiple cells using VBA. In this example, we are merging range “A1 to D10” using ‘Range.Merge’ method of range object. Now the range(“A1:D10”) will be ten cells from ‘A1:A10’ instead of ‘A1:D10’. In the above example across value is True.Please find the below screenshot for the same after merging the cells which is highlighted in yellow color.

VBA Merge Rows- Example Macro

Here is the example vba code to merge multiple rows using ‘Range.Merge’ method of range object in worksheet.

Sub sbMergeRows()
Range("5:10").Merge
End Sub

This code will merge entire rows from row 5 to row 10.

VBA Merge Column – Example Macro

Here is the example vba code to merge multiple coluns using ‘Range.Merge’ method of range object in worksheet.

Sub sbMergeColumns()
Range("B:E").Merge
End Sub

This code will merge entire columns from column B to Column E.

Please note: Do not test sbMergeColumns code with the above example (sbMergeRows). As some of the cells are common in both and it will merge the maximum possible range. Test this examples on individual worksheets to see the output.