Index Looping Through a Range

I want to move through the cells in a range using an index number (like in a For Next loop) without using a For Each statement. I have a range that is the Union of all the cells identified using the Find method. The variable rAllFound has the address:


There are four cells, as you can see. Once this range is created, I set my variable lCurrent to 1 and that allows me to work with the first cell in the range.


Now I want to increment/decrement lCurrent to use the next cell. I want this:


I get this:


These characteristics of the Range object aren’t surprising, but that doesn’t get me any closer to my goal. I could loop through rAllFound with a For Each and identify the cell I want, but I didn’t like that solution.

You’ll see in my next post that I ended up using FindNext and FindPrevious, which works just fine, but I just can’t believe there’s no way to navigate a non-contiguous range using only an index. I’m sure I’m missing something simple and obvious and I’ll appreciate it if you can set me straight.

Posted in Uncategorized

8 thoughts on “Index Looping Through a Range

  1. My guess would be something like:

    Dim rArea as Range
    Dim rCel as Range

    For Each rArea in rAllfound.Areas
    For Each rCel in rArea.Cells
    ‘Do Something
    next rCel
    Next rArea

    (or use ‘For a = 1 to areas.count’ and ‘For c = 1 to cells.count’ if you REALLY like indicies)

  2. Yes, there’s no easy way… you have to include the Areas in there. I came up with this function does seems to work:

    Sub Test()
    Dim Rng As Range
    Dim i As Long

    Set Rng = Range(“$A$2,$A$4:$A$5,$A$7?)

    For i = 1 To Rng.Count
    Debug.Print i, IndexArea(Rng, i).Address
    Next i
    End Sub

    Function IndexArea(ByVal Rng As Range, ByVal Index As Long) As Range
    Dim areaCounter As Long
    Dim i As Long

    i = 0
    i = i + 1
    areaCounter = areaCounter + Rng.Areas(i).Count
    Loop Until areaCounter > = Index

    If areaCounter = Index Then
    Set IndexArea = Rng.Areas(i).Item(Rng.Areas(i).Count)
    Set IndexArea = Rng.Areas(i).Item(areaCounter – Index)
    End If
    End Function

  3. > ?rAllFound.Address
    > $A$2,$A$4:$A$5,$A$7
    > There are four cells, as you can see.

    I think this concept is where you’re running into the problem. What you are listing there is NOT four cells, it’s three ranges (aka “areas”) that in total contain four cells. The UNION() function does just that — it creates a UNION of ranges — not a new range of individual cells. Hence the need to naviagate through the AREAS before the CELLS, as in:

    Set rAllFound = Application.Union(Range(“$A$2?), Range(“$A$4:$A$5?), Range(“$A$7?))
    For i1 = 1 To rAllFound.Areas.Count
    For i2 = 1 To rAllFound.Areas(i1).Cells.Count
    Debug.Print rAllFound.Areas(i1).Cells(i2).Address
    Next i2: Next i1

    Here’s an IMPORTANT note — the order you specify ranges in the UNION() statement may NOT be the same order they end up in as areas and cells. For example, suppose I have these three UNION() statements:

    Set rAllFound1 = Application.Union(Range(“$A$2?), Range(“$A$4:$A$5?), Range(“$A$7?))
    Set rAllFound2 = Application.Union(Range(“$A$2?), Range(“$A$4?), Range(“$A$7?), Range(“$A$5?))
    Set rAllFound3 = Application.Union(Range(“$A$2?), Range(“$A$7?), Range(“$A$4:$A$5?))

    Here are the addresses we end up with for each:

    rAllFound1.Address = $A$2,$A$4:$A$5,$A$7
    rAllFound2.Address = $A$2,$A$7,$A$4:$A$5
    rAllFound3.Address = $A$2,$A$7,$A$4:$A$5

    Note that on the rAllFound2 UNION(), the function automatically combined the $A$4 and $A$5 single-cell areas into a single area because they were adjacent. Which reiterates the point that we are not creating a new range — just a UNION() of ranges. If you add another range with $B$2 in it, the $A$2 range would move to join it.

  4. First off, in the Immediate window in the VBE,

    ? Range(“C5?).Item(3).Address(0, 0)
    ? Range(“C5?).Cells(3).Address(0, 0)

    The Item and Cells properties don’t appear to be restricted to the cells in the Range.

    I don’t see how you avoid iterating through the range either using For Each or by area then by cell within area, so best to do it only once rather than repeatedly.

    Sub test()
    Dim r As Range, c As Collection
    Set r = Range(“A2,A4:A5,A7?)
    Set c = mkcoll(r)
    Debug.Print c(1).Address(0, 0)
    Debug.Print c(2).Address(0, 0)
    Debug.Print c(3).Address(0, 0)
    Debug.Print c(4).Address(0, 0)
    Debug.Print c(5).Address(0, 0)
    End Sub

    Function makecollection(x As Object) As Collection
    Dim nc As New Collection, v As Variant
    On Error GoTo Finish
    For Each v In x
    nc.Add Item:=v
    Next v
    Set makecollection = nc
    End Function

  5. Jon: It’s not very important. In fact, IIRC, some MSKB article argues that For Each is more machine efficient. But, for most *practical* uses, it is less human efficient. In most cases where one wants to do something in sync with the control variable of a For Each loop, one is forced to implement a seperate index and manually sync it with the for each loop. Something like:
    Dim I as Integer, aRng as Range
    For each aRng…
        next aRng

    Dick: Yeah, it’s a pain. In fact, I usually “black-box” such access with something similar — though a lot simpler — than Juan Pablo’s suggestion. I imagine he had a reason for his approach but it evades me.$1236047a$ab0d@mps%3E&group=tmc.blogposts

  6. I was after similar/simple index access to cells within composite ranges.
    I found that COLLECTIONs of cells to replace the composite ranges allowed me to achieve this, viz;

    Private Function cCells(oRX As Range) As Collection
    Dim oCell As Range

    Set cCells = New Collection
    For Each oCell In oRX
    cCells.Add oCell
    Next oCell

    End Function

    ie my public functions called the cCells function…etc

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.