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

查看:2254
本文介绍了在VBA(excel)中循环遍历行最有效/最快捷的方法是什么?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我知道Excel中的VBA不是最快的 - 但是我需要最有效率(即最快速的)来循环遍历一大堆行。



目前我有:

 对于每个c在范围内($ A $ 2:$ A $& Cells(Rows。计数,A)。结束(xlUp).row 
'做东西
下一步c

做东西包括在这里和那里插入一行(所以我需要保持范围的动态查找。)



任何想法在10,000行+)?



EDIT
我已经在使用

  Application.ScreenUpdating = False 
Application.Calculation = xlManual


解决方案

如果您只是在列A中循环10k行,则将该行转储到变量数组中,然后循环。



将元素添加到新数组(需要时添加行),并使用Transpose()将数组放入您的范围一动,或者您可以使用迭代器变量跟踪您所在的行,并以这种方式添加行。




$

$ b varray = Range(A2:A&单元格(Rows.Count,A)。End(xlUp).Row).Value

For i = 1 To UBound(varray,1)
' ,1)
下一个

这是一个例子,说明如何在评估每个细胞。此示例仅在列A中的每个行之后插入一行。在行中,从A2开始,插入期间将+2添加到变量i中。如果我们用A1启动我们的数组,它将为+1。

 子测试()

Dim varray As Variant
Dim i As Long

varray = Range(A2:A10)。值

'必须退后或者将是无限的循环
对于i = UBound(varray,1)到LBound(varray,1)步骤-1
'你的逻辑和评估在这里
如果varray(i,1)=foo然后
'不如何抵消我的变量
范围(A& i + 2).EntireRow.Insert
结束如果
下一个

End Sub


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.

Currently I have:

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.)

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

EDIT I am already using

Application.ScreenUpdating = False
Application.Calculation = xlManual

解决方案

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

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

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆