Passing through merged cells in VBA

Question

Is it possible to iterate over merged cells in vba .

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

Answer 1

In the above answers you can sort.

If you do not know where the merged cells are located, you can use the following procedure to quickly find them.

When I built Mappit! I realized that when I developed the combined reporting of cells that combined cells were part of xlBlanks

That way, you can use the code to immediately detect merged cells, and not for every cell test for the MergedCells property, which is 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 

Answer 2

Here is the first blow to your problem:

 Option Explicit Sub loopOverCells() Dim rCell As Range Dim i As Integer Set rCell = [B1] For i = 1 To 6 Debug.Print rCell.Address Set rCell = rCell.Offset(1, 0) ' Jump 1 row down to the next cell Next i End Sub 

Answer 3

A little worse, a similar idea:

 Option Explicit Sub ListValues() Dim i As Long For i = 4 To 40 Step 6 Debug.Print Range("B" & i).Value Next i End Sub