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

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

This question is related to vba loops excel

The answer is


EDIT Summary and reccomendations

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

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.

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

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 bad and 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)

Test case 1: loop an array

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, "#,###")

Result:

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

Test Case 2: loop the range

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, "#,###")

Result:

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

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

Test 3: loop an array with a cell reference

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, "#,###")

Result:

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

Test case 4: loop range with a cell reference

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, "#,###")

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

  • ScreenUpdating off,
  • Calulation manual,
  • Events disabled.

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


once with for each Dkey in dDict,


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

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


Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to loops

How to increment a letter N times per iteration and store in an array? Angular 2 Cannot find control with unspecified name attribute on formArrays What is the difference between i = i + 1 and i += 1 in a 'for' loop? Prime numbers between 1 to 100 in C Programming Language Python Loop: List Index Out of Range JavaScript: Difference between .forEach() and .map() Why does using from __future__ import print_function breaks Python2-style print? Creating an array from a text file in Bash Iterate through dictionary values? C# Wait until condition is true

Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?