VBA Merge Cells & Unmerge Cells

In this tutorial, we are going to look at the different ways you can merge cells using VBA.

Merge Cells Using VBA

You can merge cells in VBA using the Range.Merge method. The following code will merge cells A1:C1:

Sub MergingCells()

Range("A1:C1").Merge

End Sub

The result is:

Unmerge Cells Using VBA

You can unmerge a merged cell range using the Range.UnMerge method. Let’s say you’ve merged the cell range A1:D1. You can use one of the cell references in the range to unmerge the cells. The following code shows you how to unmerge cells A1:D1, in this case we are using cell B1:

Sub UnmergeCells()

Range("B1").UnMerge

End Sub

More Merge Examples

Merge Rows Using VBA

You can merge rows using VBA by specifying the row numbers that you want to merge. We have the text in cell A1 shown below:

The following code will merge rows 1-4:

Sub MergeRows()

Range("1:4").Merge

End Sub

The result is:

Merge Columns Using VBA

You can merge columns using VBA and the column letters. The following code will merge columns A:C.

Sub MergeColumns()

Range("A:C").Merge

End Sub

The result is:

Merge and Center Cell Contents Horizontally

You can merge and center cell contents horizontally across a cell range. You have to merge the cells of interest and also set the horizontal alignment property of the range object to center. We have the text in cell A1 as shown below:

The following code will merge the cell range A1:D1 and center the contents of cell A1 horizontally across this cell range:

Sub MergeandCenterContentsHorizontally()

Range("A1:D1").Merge
Range("A1:D1").HorizontalAlignment = xlCenter

End Sub

The result is:

Merge and Center Cell Contents Vertically

You can merge and center cell contents vertically across a cell range. You have to merge the cells of interest and also set the vertical alignment property of the range object to center. We have the text in cell A1 as shown below:

The following code will merge the cell range A1:A4 and center the contents of cell A1 vertically across this cell range:

Sub MergeandCenterContentsVertically()

Range("A1:A4").Merge
Range("A1:D1").VerticalAlignment = xlCenter

End Sub

The result is:

Merge Cells Across a Cell Range

You can merge cells across a cell range. You have to set the optional Across parameter of the Merge method to True. We have the text in cell A1 as shown below:

The following code will merge the cell range A1:D1:

Sub MergeCellsAcross()

Range("A1:D1").Merge Across:=True

End Sub

The result is:

Determine Start and End Columns of Merged Cells

Here is example code to determine the Start and End Column of a Merged Cell. It assigns the value of each column to a variable and returns them in a message box.

Public Sub StartEndMerge()
  'Declare Variables
  Dim StartColumn As Integer
  Dim EndColumn As Integer
  'Assign Variables
  StartColumn = ActiveCell.Column
  EndColumn = Selection.Columns.Count + StartColumn - 1

  'Show Results
  MsgBox "Start Column " & StartColumn
  MsgBox "End Column " & EndColumn

End Sub