seleccionar - ¿Cuál es la forma más rápida/eficiente de recorrer las filas en VBA(excel)?

recorrer un rango en excel vba

Question

Sé que VBA en Excel no es lo más rápido, pero necesito la forma más eficiente (es decir, más rápida) de recorrer una gran muestra de filas.

Actualmente tengo:

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

El "hacer cosas" incluye insertar una fila aquí y allá (así que necesito mantener la búsqueda dinámica del rango).

¿Alguna idea (mirando 10.000 filas +)?

EDITAR ya estoy usando

Application.ScreenUpdating = False
Application.Calculation = xlManual

Answer 1

Porque Cada es mucho más rápido que para I = 1 a X, por alguna razón. Solo trata de pasar por el mismo diccionario,

una vez con para cada Dkey en dDict,

y una vez con para Dkey = lbound (dDict.keys) a ubound (dDict.keys)

=> Notará una gran diferencia, aunque esté atravesando la misma construcción.

Answer 2

Si solo está haciendo un bucle a través de 10k filas en la columna A, entonces voltee la fila en una matriz variante y luego haga un bucle a través de eso.

Luego puede agregar los elementos a una nueva matriz (mientras agrega filas cuando sea necesario) y usar Transpose () para colocar la matriz en su rango en un solo movimiento, o puede usar su variable de iterador para rastrear en qué fila está y agregar filas de esa manera.

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

Aquí hay un ejemplo de cómo podría agregar filas después de evaluar cada celda. Este ejemplo solo inserta una fila después de cada fila que tiene la palabra "foo" en la columna A. No es que se agregue "+2" a la variable i durante la inserción, ya que estamos comenzando en A2. Sería +1 si estuviéramos empezando nuestra matriz con 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

Answer 3

EDITAR Resumen y recomendaciones

Usar una estructura de for each cell in range no es en sí lento. Lo que es lento es el acceso repetido a Excel en el bucle (ya sea leer o escribir valores de celda, formato, etc., insertar / eliminar filas, etc.).

Lo que es demasiado lento depende enteramente de sus necesidades. Un Sub que tarda minutos en ejecutarse podría estar bien solo se usa raramente, pero otro que tarda 10 segundos puede ser demasiado lento si se ejecuta con frecuencia.

Por lo tanto, algunos consejos generales:

1. mantenlo simple al principio Si el resultado es demasiado lento para sus necesidades, entonces optimice
2. Centrarse en la optimización del contenido del bucle.
3. no asuma simplemente que se necesita un bucle. Hay alternativas en algún momento
4. si necesita usar valores de celda (mucho) dentro del bucle, cárguelos en una matriz variante fuera del bucle.
5. una buena manera de evitar la complejidad con inserciones es hacer un bucle desde abajo hacia arriba
   ( for index = max to min step -1 )
6. si no puede hacer eso y su ''insertar una fila aquí y allá'' no es demasiada, considere volver a cargar la matriz después de cada inserción
7. Si necesita acceder a propiedades de celda que no sean un value , está atascado con referencias de celda
8. Para eliminar un número de filas, considere construir una referencia de rango para un rango de múltiples áreas en el bucle, luego borre ese rango de una vez después del bucle

por ejemplo (no probado!)

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

Post original

La sabiduría convencional dice que el bucle a través de las celdas es malo y el bucle a través de una matriz variante es bueno . Yo también he sido un defensor de esto por algún tiempo. Tu pregunta me hizo pensar, así que hice algunas pruebas cortas con resultados sorprendentes (para mí de todos modos):

conjunto de datos de prueba: una lista simple en las celdas A1 .. A1000000 (eso es 1,000,000 de filas)

Caso de prueba 1: bucle de una matriz

Dim v As Variant
Dim n As Long
T1 = GetTickCount
Set r = Range("$A$1", 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, "#,###")

Resultado:

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

Caso de prueba 2: recorrer el rango

T1 = GetTickCount
Set r = Range("$A$1", 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, "#,###")

Resultado:

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

Por lo tanto, hacer un bucle en una matriz es más rápido, pero solo en un 19%, mucho menos de lo que esperaba.

Prueba 3: bucle una matriz con una referencia de celda

T1 = GetTickCount
Set r = Range("$A$1", 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, "#,###")

Resultado:

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

Caso de prueba 4: rango de bucle con una referencia de celda

T1 = GetTickCount
Set r = Range("$A$1", 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, "#,###")

Resultado:

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

Entonces, evento con una sola referencia de celda simple, el bucle es un orden de magnitud más lento, y además, ¡el bucle de rango es dos veces más rápido!

Entonces, la conclusión es que lo que más importa es lo que haces dentro del bucle , y si la velocidad realmente importa, prueba todas las opciones

FWIW, probado en Excel 2010 32 bit, Win7 64 bit Todas las pruebas con

• ScreenUpdating desactivado,
• Manual de Calulation ,
• Events deshabilitados.