Table of Contents
Using VBA, it is possible to merge cells in a worksheet that have the same values by looping through the cells in the range and merging any two cells that have the same value. The code needed for this is fairly simple and involves setting the range to loop through, checking the value of each cell in the range, and then merging any cells that have the same value. Once all the cells with similar values are merged, the loop is exited. This is a great way to quickly create a summary table or to join cells with related information.
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.