在 VBA (excel) 中遍历行的最有效/最快捷的方法是什么? [英] What is the most efficient/quickest way to loop through rows in VBA (excel)?

问题描述

我知道 Excel 中的 VBA 不是最快的 - 但我需要最有效(即最快)的方式来循环遍历大量行样本.

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.

目前我有:

For Each c In Range("$A$2:$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.)

任何想法(查看 10,000 行+)?

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

编辑我已经在使用

Application.ScreenUpdating = False
Application.Calculation = xlManual

推荐答案

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

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

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

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.

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

以下示例说明了如何在评估每个单元格后添加行.此示例只是在 A 列中包含单词foo"的每一行之后插入一行.并不是在插入期间将+2"添加到变量 i 中，因为我们是从 A2 开始的.如果我们以 A1 开始我们的数组，它将是 +1.

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.

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

这篇关于在 VBA (excel) 中遍历行的最有效/最快捷的方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！