vba - Reverse order of For Each loop

Question

One of the most powerful things about VB is ability to loop through objects in a collection WITHOUT referring to the index - for each loop.

I find it very useful only want to remove objects from a collection.

When doing removing objects from a predefined such as rows on a spread sheet the code is simpler if I use indexing and start at the largest and work back to the first. (Step -1 with an iterator) (otherwise requires an offset as the For each moves the enumerator pointer back to the previous object once the active one is deleted)

eg.

For intA = 10 to 1 step -1 
    ' ...
Next

What about when using a For Each | Next eg.

For each rngCell in Selection.Cells
    ' ...
Next

How could I loop backwards using the for each loop syntax?

Answer 1

It's not possible to loop backwards using the for each loop syntax.

As an alternative you can use a For i = a To 1 Step -1 loop:

Sub reverseForEach()
    Dim i As Long, rng As Range

    Set rng = ActiveSheet.Range("A1:B2")

    For i = rng.Cells.Count To 1 Step -1

        Debug.Print rng.item(i).Address
        ' Or shorthand rng(i) as the Item property 
        ' is the default property for the Range object.
        ' Prints: $B$2, $A$2, $B$1, $A$1

    Next i

End Sub

This works with all collections that have the Item property. For instance Worksheets, Areas or Shapes.

Note: The order of the loop when using on the Range object is from right to left, then up.