在 VBA (excel) 中遍历行的最有效/最快捷的方法是什么？

What is the most efficient/quickest way to loop through rows in VBA (excel)?

提问by Chris

I know VBA in Excel isn't the quickest of things - but I need the most efficient (i.e. quickest) way to loop through a large sample of rows.

我知道 Excel 中的 VBA 不是最快的东西 - 但我需要最有效（即最快）的方式来循环遍历大量行样本。

Currently I have:

目前我有：

For Each c In Range("$A:$A$" & Cells(Rows.count, "A").End(xlUp).row
    ' do stuff
Next c

The 'do stuff' includes insert a row here and there (so I need to keep the dynamic lookup of the range.)

“做事”包括在这里和那里插入一行（所以我需要保持范围的动态查找。）

Any ideas (looking at 10,000 rows+)?

任何想法（查看 10,000 行+）？

EDIT I am already using

编辑我已经在使用

Application.ScreenUpdating = False
Application.Calculation = xlManual

回答by aevanko

If you are just looping through 10k rows in column A, then dump the row into a variant array and then loop through that.

如果您只是循环遍历 A 列中的 10k 行，则将该行转储到一个变体数组中，然后循环遍历该行。

You can then either add the elements to a new array (while adding rows when needed) and using Transpose() to put the array onto your range in one move, or you can use your iterator variable to track which row you are on and add rows that way.

然后，您可以将元素添加到新数组（同时在需要时添加行）并使用 Transpose() 将数组一次性放入您的范围，或者您可以使用迭代器变量来跟踪您所在的行并添加那样行。

Dim i As Long
Dim varray As Variant

varray = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value

For i = 1 To UBound(varray, 1)
    ' do stuff to varray(i, 1)
Next

Here is an example of how you could add rows after evaluating each cell. This example just inserts a row after every row that has the word "foo" in column A. Not that the "+2" is added to the variable i during the insert since we are starting on A2. It would be +1 if we were starting our array with A1.

下面是如何在评估每个单元格后添加行的示例。此示例只是在 A 列中包含单词“foo”的每一行之后插入一行。由于我们从 A2 开始，因此在插入期间不会将“+2”添加到变量 i。如果我们从 A1 开始我们的数组，它将是 +1。

Sub test()

Dim varray As Variant
Dim i As Long

varray = Range("A2:A10").Value

'must step back or it'll be infinite loop
For i = UBound(varray, 1) To LBound(varray, 1) Step -1
    'do your logic and evaluation here
    If varray(i, 1) = "foo" Then
       'not how to offset the i variable 
       Range("A" & i + 2).EntireRow.Insert
    End If
Next

End Sub

回答by chris neilsen

EDITSummary and reccomendations

编辑摘要和建议

Using a for each cell in rangeconstruct is not in itselfslow. What isslow is repeated access to Excel in the loop (be it reading or writing cell values, format etc, inserting/deleting rows etc).

使用for each cell in range构造本身并不慢。什么是慢则到Excel多次访问中环（无论是读取或写入单元格的值，等格式，插入/删除行等）。

What is too slow depends entierly on your needs. A Sub that takes minutes to run might be OK if only used rarely, but another that takes 10s might be too slow if run frequently.

什么太慢完全取决于您的需求。一个需要几分钟才能运行的 Sub 如果很少使用可能没问题，但如果频繁运行，另一个需要 10 秒的 Sub 可能会太慢。

So, some general advice:

所以，一些一般性建议：

1. keep it simple at first. If the result is too slow for your needs, then optimise
2. focus on optimisation of the content of the loop
3. don't just assume a loop is needed. There are sometime alternatives
4. if you need to use cell values (a lot) inside the loop, load them into a variant array outside the loop.
5. a good way to avoid complexity with inserts is to loop the range from the bottom up
   (for index = max to min step -1)
6. if you can't do that and your 'insert a row here and there' is not too many, consider reloading the array after each insert
7. If you need to access cell properties other than value, you are stuck with cell references
8. To delete a number of rows consider building a range reference to a multi area range in the loop, then delete that range in one go after the loop

1. 首先保持简单。如果结果对于您的需求来说太慢了，那么优化
2. 重点优化循环内容
3. 不要只是假设需要一个循环。有时有替代品
4. 如果您需要在循环内使用单元格值（很多），请将它们加载到循环外的变体数组中。
5. 避免插入复杂性的一个好方法是从下往上循环范围
   ( for index = max to min step -1)
6. 如果您不能这样做并且您的“在此处插入一行”并没有太多，请考虑在每次插入后重新加载数组
7. 如果您需要访问 以外的单元格属性value，则您会遇到单元格引用
8. 要删除多行，请考虑构建对循环中多区域范围的范围引用，然后在循环后一次性删除该范围

eg (not tested!)

例如（未测试！）

Dim rngToDelete as range
for each rw in rng.rows
    if need to delete rw then

        if rngToDelete is nothing then
            set rngToDelete = rw
        else
            set rngToDelete = Union(rngToDelete, rw)
        end if

    endif
next
rngToDelete.EntireRow.Delete

Original post

原帖

Conventional wisdom says that looping through cells is badand looping through a variant array is good. I too have been an advocate of this for some time. Your question got me thinking, so I did some short tests with suprising (to me anyway) results:

传统观点认为循环单元格不好，循环变量数组好。一段时间以来，我也一直是这一点的倡导者。你的问题让我思考，所以我做了一些简短的测试，结果令人惊讶（无论如何对我来说）：

test data set: a simple list in cells A1.. A1000000(thats 1,000,000 rows)

测试数据集：单元格中的一个简单列表A1.. A1000000（即 1,000,000 行）

Test case 1: loop an array

测试用例 1：循环数组

Dim v As Variant
Dim n As Long

T1 = GetTickCount
Set r = Range("$A", Cells(Rows.Count, "A").End(xlUp)).Cells
v = r
For n = LBound(v, 1) To UBound(v, 1)
    'i = i + 1
    'i = r.Cells(n, 1).Value 'i + 1
Next
Debug.Print "Array Time = " & (GetTickCount - T1) / 1000#
Debug.Print "Array Count = " & Format(n, "#,###")

Result:

结果：

Array Time = 0.249 sec
Array Count = 1,000,001

Test Case 2: loop the range

测试用例 2：循环范围

T1 = GetTickCount
Set r = Range("$A", Cells(Rows.Count, "A").End(xlUp)).Cells
For Each c In r
Next c
Debug.Print "Range Time = " & (GetTickCount - T1) / 1000#
Debug.Print "Range Count = " & Format(r.Cells.Count, "#,###")

Result:

结果：

Range Time = 0.296 sec
Range Count = 1,000,000

So,looping an array isfaster but only by 19% - much less than I expected.

因此，循环数组是快，但只有19％ -远远低于我的预期。

Test 3: loop an array with a cell reference

测试 3：使用单元格引用循环数组

T1 = GetTickCount
Set r = Range("$A", Cells(Rows.Count, "A").End(xlUp)).Cells
v = r
For n = LBound(v, 1) To UBound(v, 1)
    i = r.Cells(n, 1).Value
Next
Debug.Print "Array Time = " & (GetTickCount - T1) / 1000# & " sec"
Debug.Print "Array Count = " & Format(i, "#,###")

Result:

结果：

Array Time = 5.897 sec
Array Count = 1,000,000

Test case 4: loop range with a cell reference

测试用例 4：使用单元格引用循环范围

T1 = GetTickCount
Set r = Range("$A", Cells(Rows.Count, "A").End(xlUp)).Cells
For Each c In r
    i = c.Value
Next c
Debug.Print "Range Time = " & (GetTickCount - T1) / 1000# & " sec"
Debug.Print "Range Count = " & Format(r.Cells.Count, "#,###")

Result:

结果：

Range Time = 2.356 sec
Range Count = 1,000,000

So event with a single simple cell reference, the loop is an order of magnitude slower, and whats more, the range loop is twice as fast!

因此，对于单个简单单元格引用的事件，循环慢了一个数量级，更重要的是，范围循环快了两倍！

So, conclusion is what matters most is what you do inside the loop, and if speed really matters, test all the options

所以，结论是最重要的是你在循环中做什么，如果速度真的很重要，测试所有选项

FWIW, tested on Excel 2010 32 bit, Win7 64 bit All tests with

FWIW，在 Excel 2010 32 位、Win7 64 位上测试所有测试均使用

• ScreenUpdatingoff,
• Calulationmanual,
• Eventsdisabled.

• ScreenUpdating离开，
• Calulation手动的，
• Events禁用。

回答by Dumitru Daniel

For Each is much faster than for I=1 to X, for some reason. Just try to go through the same dictionary,

由于某种原因，For Each 比 I=1 到 X 快得多。试着翻阅同一本字典，

---

---

once with for each Dkey in dDict,

对 dDict 中的每个 Dkey 使用一次，

---

---

and once with for Dkey = lbound(dDict.keys) to ubound(dDict.keys)

和一次 for Dkey = lbound(dDict.keys) 到 ubound(dDict.keys)

=>You will notice a huge difference, even though you are going through the same construct.

=>即使您正在经历相同的构造，您也会注意到巨大的差异。