VBA: How to Merge Cells with the Same Values


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:

VBA merge cells with the same values

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

Leave a Reply

Your email address will not be published. Required fields are marked *