CODEWITHSUNDEEP
vbaexcelforeach
sirplus
sirplus

Reputation: 535

Reverse order of For Each loop

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?

Upvotes: 39

Views: 92809

Answers (6)

nbougiou
nbougiou

Reputation: 61

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

Upvotes: 0

Malamare
Malamare

Reputation: 27

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

Upvotes: 0

ashleedawg
ashleedawg

Reputation: 21619

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

Upvotes: 6

Hubisan
Hubisan

Reputation: 1172

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.

Upvotes: 40

CETAB
CETAB

Reputation: 21

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

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53137

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.

Upvotes: 20

Related Questions