VBA – Merge horizontal cells with equal values in Excel

If you work as a VBA developer long enough, you may find quite interesting tasks to appear in front of you. Sometimes you have to merge horizontal cells with equal values. And because you are not going to do in manually, you need a kind of code, to do it for you. This code exists and it really works out quite well.

So, here is what we have:

What we want to do, is to merge horizontally every cell, which has a value, equal to the next one. Pretty much, we want to receive this as an outcome:

And we want to have it stable and reusable. How can we do it with VBA? One of the answers is quite interesting – with offset and for-each loop. Here it goes:

Sub MergeCells()

    Dim rngMerge    As Range
    Dim cell        As Range
    
    Set rngMerge = Range("A2:M7")
    Range("A2:M7").MergeCells = False

MergeAgain:
    For Each cell In rngMerge
        If cell.Value = cell.Offset(0, 1).Value And IsEmpty(cell) = False Then
            Range(cell, cell.Offset(0, 1)).Merge
            GoToMergeAgain
        End If
    Next

End Sub

The interesting part is that we allow ourself to go out of the loop for every cell we merge and go inside again. Probably you should play a bit with F8 to get the idea!

Enjoy it!

🙂