VBA Merge Cells & Unmerge Cells

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on March 14, 2024

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:

How to Merge Cells in VBA

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:

Merging Rows Using VBA

The following code will merge rows 1-4:

Sub MergeRows()

Range("1:4").Merge

End Sub

The result is:

Merging Rows Using VBA

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:

Merging Columns In VBA

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:

Merge Cells Horizontally Using VBA

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:

Merging and Centering Cells Contents in VBA Horizontally

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:

Merging Cells in VBA using Vertical Alignment

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:

Merging Cells Using Vertical Alignment in VBA

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:

Merge Cells Across in VBA

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

Sub MergeCellsAcross()

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

End Sub

The result is:

Merge Cells Across in VBA

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
vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples