Excel VBA Loop Range

Topic

New Reply

I am trying to loop through the rows in a selected range.

This works fine if the range is continuous. However, if the range is discontinous e.g.Sheet1!$A$8,Sheet1!$A$10:$A$12 which it will more than likely be for this task, when I pass the range object to another procedure it seems to contain only the first cell.

I’m looping through the rows like so:

For n = 1 To Rng.Rows.Count
Debug.Print Rng.Cells(n, 1)
Next n

but using the example range above, it thinks it only has one row.

After selecting the range I have coloured the cells using MyRng.Interior.ColorIndex = 6 and written the correct address to a text box on a form using MyRng.Address(False, False) so the range object is what I think it should be at that point.

Any thought?

Reply | Quote

Replies

You need to loop through each area:

Code:

Sub DoTheLoop()
    Dim oArea As Range
    Dim oCell As Range
    For Each oArea In oRng.Areas
        For Each oCell In oArea.Cells
            Debug.Print oCell.Address & "|" & oCell.Value
        Next
    Next
End Sub

Reply | Quote

Darsha,

It appears that the rng.rows.count will only parse the first range in a non-contiguous range set.
See the test code below:

Code:

Sub Test()

Dim n   As Long
Dim Rng As Range
Dim Cnt As Long

Cnt = 0
Set Rng = Selection
  Debug.Print "Range: " & Rng.Address & " Rows: " & Rng.Rows.Count; ""
For n = 1 To Rng.Rows.Count
 Cnt = Cnt + 1
'Debug.Print Rng.Cells(n, 1)
Next n
  Debug.Print "n= : " & Cnt
  Debug.Print "Range: " & Rng.Address & " Rows: " & Rng.Rows.Count; ""

End Sub

------------- OUTPUT -----------

Range: $A$1:$J$13,$M$22:$S$28 Rows: 13
n= : 13
Range: $A$1:$J$13,$M$22:$S$28 Rows: 13

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote