40

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?

9
  • You can simply reverse the collection and do a ForEach :) Commented Jun 23, 2014 at 4:10
  • 2
    I didn't even know you could do this: stackoverflow.com/questions/4203329/…. Good to know. Commented Jun 23, 2014 at 4:18
  • 4
    @VBlades, that is interesting, but for this question won't the OP then be faced with the same problem just with a new collection? I think the best solution when deleting objects in a VBA collection like this is to just use the collections index and a counter. Commented Jun 23, 2014 at 5:04
  • 3
    @DougGlancy: Yes, maybe Doug. Tbh, I am at work and just found it novel and possibly related to OP's problem, so I linked it. I think the bigger principle, though, is that For...Each constructs are specifically for those cases when the order is unimportant (stackoverflow.com/questions/952136/…); kind of like set theory with rows of data - unordered by nature. This implies that yeah, if you need to do things in a specific order, use the counters supplied by the collections you are working with. Commented Jun 23, 2014 at 5:22
  • 4
    Question why you want to do this - the workarounds required will render the code less efficient than the Step alternative? Commented Jun 24, 2014 at 0:01

6 Answers 6

40

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.

Sign up to request clarification or add additional context in comments.

Comments

20

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.

2 Comments

This is the answer "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."
In short even if the collection is reversed for each cant be used when deleting as For Each moves the enumerator pointer back to the previous object once the active one is deleted. ie. introduces an offset error The more things deleted the bigger this becomes so offset needs to be used in conjunction with a counter. Much simpler to use indexing and reverse counter in the first place.
6

There are other good answers but here's another alternative method of "stepping backwards" through a Range.


Function to Invert Range into Array

This function returns a "backwards Range Array" that can be used with For..Each:

Function ReverseRange(rg As Range) As Range()
    Dim arr() As Range, r As Long, c As Long, n As Long
    With rg
        ReDim arr(1 To .Cells.Count) 'resize Range Array
        For r = .Cells(.Rows.Count, 1).Row To .Cells(1, 1).Row Step -1
            For c = .Cells(1, .Columns.Count).Column To .Cells(1, 1).Column Step -1
                n = n + 1
                Set arr(n) = .Worksheet.Cells(r, c) 'set cell in Array
            Next c
        Next r
    End With
    ReverseRange = arr  'return Range Array as function result
End Function

Example Usage:

Sub test()
    Dim oCell
    For Each oCell In ReverseRange(ActiveSheet.Range("E5:A1"))

        Debug.Print oCell.Address 'do something here with each cell

    Next oCell
End Sub

Comments

0

use a second variable that is set as your wanted counter and use this one in your code

'ex: Loop from n = 19 to 16
For i = 0 To 3
   n = 19 - i
   'your code here using n as the counter
Next

Comments

0

Only for Range collections. They are more complicated if they have more than 1 Area.

Basically there are two loops, the first one keeps the index of all the cells in an array and the second one creates a union of ranges from back to front

Option Explicit

Private Sub Main()
    Dim InvertedRange As Range
    Set InvertedRange = InvertRange(Application.Union(ActiveSheet.Range("A1:A2"), _
      ActiveSheet.Range("F6:F7"), ActiveSheet.Range("E4:F5"), ActiveSheet.Range("E1")))
    Dim ActualRange As Range
    For Each ActualRange In InvertedRange
        Debug.Print (ActualRange.Address(False, False) & " : " & ActualRange.Value)
    Next ActualRange
End Sub

Public Function InvertRange(ByVal rngRange_I As Range) As Range
    Dim RangesArray() As Long
    ReDim RangesArray(1 To rngRange_I.Count, 1 To rngRange_I.Count)
    Dim ActualArea As Range
    Dim ActualRange As Range
    Dim ArrayIndex As Long
    For Each ActualArea In rngRange_I.Areas
        For Each ActualRange In ActualArea
            ArrayIndex = ArrayIndex + 1
            RangesArray(ArrayIndex, 1) = ActualRange.Row
            RangesArray(ArrayIndex, 2) = ActualRange.Column
        Next ActualRange
    Next ActualArea

    Dim ActualRow As Long
    Dim ActualColumn As Long
    ActualRow = RangesArray(UBound(RangesArray, 1), 1)
    ActualColumn = RangesArray(UBound(RangesArray, 2), 2)
    With rngRange_I.Worksheet
        Dim InvertedRange As Range
        Set InvertedRange = .Cells(ActualRow, ActualColumn)
        For ArrayIndex = UBound(RangesArray, 1) To LBound(RangesArray, 1) Step -1
            ActualRow = RangesArray(ArrayIndex, 1)
            ActualColumn = RangesArray(ArrayIndex, 2)
            Set InvertedRange = Application.Union(InvertedRange, _
              .Cells(ActualRow, ActualColumn))
        Next ArrayIndex
    End With

    Set InvertRange = InvertedRange
End Function

Comments

0

You can use a stack (LIFO data structure) for inventing your list and the code would be something like this:

   Dim aStack as Object

   Set aStack = CreateObject("System.Collections.Stack") 
    
   For Each arngCell in Selection.Cells
       aStack.Push(arngCell)
   Next
    
   While aStack.Count > 0 
    
       rngCell = aStack.Pop

       ' ...
    
    End While 

    Set stack = Nothing

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.