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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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!
🙂