vba For Each 循环的逆序

Reverse order of For Each loop

提问by sirplus

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

VB 最强大的功能之一是能够在不引用索引的情况下遍历集合中的对象 -for each循环。

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)

当从预定义的对象（例如电子表格上的行）中删除对象时，如果我使用索引并从最大的开始并返回到第一个，则代码会更简单。（使用迭代器的步骤 -1）（否则需要一个偏移量，因为一旦活动对象被删除，For each 会将枚举器指针移回前一个对象）

eg.

例如。

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

What about when using a For Each | Next eg.

使用 For Each 时怎么样？接下来例如。

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

How could I loop backwardsusing the for eachloop syntax?

如何使用循环语法向后for each循环？

采纳答案by Hubisan

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

不可能使用 for each 循环语法向后循环。

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

作为替代方案，您可以使用For i = a To 1 Step -1循环：

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, $A, $B, $A

    Next i

End Sub

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

这适用于所有具有 Item 属性的集合。例如工作表、区域或形状。

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

注意：在 Range 对象上使用时循环的顺序是从右到左，然后是上。

回答by chris neilsen

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

对于内置集合（例如 a Range），简短的回答是：你不能。对于用户定义的集合，@VBlades 链接的答案可能有用，尽管成本可能超过收益。

One work around is to seperate the identificationof 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 Rangeexample, you can also take advantage of the Variant Arraymethod to further speed things up.

一种变通方法是将要移除的项目的标识与实际移除分开。例如，对于一个范围，使用 建立一个新的范围变量Union，然后处理该变量，例如一次性删除所有行。对于Range例如，你也可以利用的Variant Array方法，以进一步加快速度。

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

这些是否有用取决于您的实际用例。

回答by ashleedawg

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:

此函数返回一个“向后范围数组”，可用于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

回答by Malamare

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

仅适用于范围集合。如果它们有 1 个以上的区域，它们会更复杂。

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

回答by CETAB

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