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:
Dim rngMerge As Range
Dim cell As Range
Set rngMerge = Range("A2:M7")
Range("A2:M7").MergeCells = False
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
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!