You can use the following syntax in VBA to merge cells with the same values in a particular range:
Sub MergeSameCells()
'turn off display alerts while merging
Application.DisplayAlerts = False
'specify range of cells for merging
Set myRange = Range("A1:C13")
'merge all same cells in range
MergeSame:
For Each cell In myRange
If cell.Value = cell.Offset(1, 0).Value And Not IsEmpty(cell) Then
Range(cell, cell.Offset(1, 0)).Merge
cell.VerticalAlignment = xlCenter
GoTo MergeSame
End If
Next
'turn display alerts back on
Application.DisplayAlerts = True
End Sub
This particular macro merges cells with the same values in the range A1:C13.
The following example shows how to use this syntax in practice.
Example: Merge Cells with the Same Values in VBA
Suppose we have the following dataset in Excel that contains information about points scored by various basketball players:
Suppose we would like to merge the cells with the same values in consecutive rows.
We can create the following macro to do so:
Sub MergeSameCells()
'turn off display alerts while merging
Application.DisplayAlerts = False
'specify range of cells for merging
Set myRange = Range("A1:C13")
'merge all same cells in range
MergeSame:
For Each cell In myRange
If cell.Value = cell.Offset(1, 0).Value And Not IsEmpty(cell) Then
Range(cell, cell.Offset(1, 0)).Merge
cell.VerticalAlignment = xlCenter
GoTo MergeSame
End If
Next
'turn display alerts back on
Application.DisplayAlerts = True
End Sub
When we run this macro, we receive the following output:
Notice that each of the cells that contained the same Conference name and Team names have been merged.
Note that we used the statement cell.VerticalAlignment = xlCenter to specify that the text should be centered vertically in the cells that are merged together.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Number of Used Columns
VBA: How to Change Row Height
VBA: How to Change Column Width