excel - Looping through Merged cells in VBA

Question

Is it possible to loop through merged cells in vba.

• I have 6 merged cells in the range B4:B40
• I need the values in these 6 cells 6 iterations only.

Answer 1

The above answers look to have you sorted.

If you don't know where the merged cells are then you can use the following routine to quickly detect them.

When I built Mappit! I realised that when I developed merged cell reporting that merged cells were part of xlBlanks

So you can use the code to detect merged cells immediately rather than loop through each cell testing for the MergedCells property being true.

Sub DetectMerged()
Dim rng1 As Range
Dim rng2 As Range
On Error Resume Next
Set rng1 = Intersect(Cells.SpecialCells(xlFormulas), Cells.SpecialCells(xlBlanks))
Set rng2 = Intersect(Cells.SpecialCells(xlConstants), Cells.SpecialCells(xlBlanks))
On Error GoTo 0
If Not rng1 Is Nothing Then MsgBox "Merged formulae cells in " & rng1.Address(0, 0)
If Not rng2 Is Nothing Then MsgBox "Merged constant cells in " & rng2.Address(0, 0)
End Sub