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.

Answer 2

For built in collections (eg a Range) the short answer is: you can't. For user defined collections the answer linked by @VBlades might be useful, although the cost might outweigh the benifit.

One work around is to seperate the identification of items to be removed from the actual removal. Eg, for a range, build up a new range variable using Union, then process that variable, eg delete all the rows in one go. For the Range example, you can also take advantage of the Variant Array method to further speed things up.

Whether or not any of this is useful will depend on your actual use case.