VBA Array vs Range Performance: Timer-Tested on 50,000 Rows

This guide is part of the FinDataPro Excel VBA Workbook Events series.

Timer-Tested: Range Loop vs Array Loop

Run both against the same 50,000-row column and compare:

Option Explicit

Sub TimeRangeLoop()
    Dim ws As Worksheet
    Dim i As Long
    Dim startTime As Double
    Set ws = ThisWorkbook.Sheets("Data")

    startTime = Timer
    For i = 1 To 50000
        ws.Cells(i, 1).Value = ws.Cells(i, 1).Value * 1.1
    Next i
    Debug.Print "Range loop: "; Timer - startTime; "seconds"
End Sub

Sub TimeArrayLoop()
    Dim ws As Worksheet
    Dim i As Long
    Dim startTime As Double
    Dim data As Variant
    Set ws = ThisWorkbook.Sheets("Data")

    startTime = Timer
    data = ws.Range("A1:A50000").Value
    For i = 1 To 50000
        data(i, 1) = data(i, 1) * 1.1
    Next i
    ws.Range("A1:A50000").Value = data
    Debug.Print "Array loop: "; Timer - startTime; "seconds"
End Sub

Run both on your own machine — the array version routinely comes back an order of magnitude faster.

Where the Code Goes

Both subs live in a standard module purely for benchmarking. Once proven, the array pattern replaces the loop inside Loop Through Range of Cells whenever that range grows past a few thousand rows.

Finance Use Case: Large ERP Extracts

A general ledger extract with tens of thousands of transaction lines is exactly where a direct Range loop becomes genuinely slow — the difference between a macro that runs in two seconds and one that takes a minute and a half is entirely down to whether the loop reads and writes to the sheet on every single row, or once at the start and once at the end.

Why the Gap Is So Large

Every reference to a cell — read or write — crosses from VBA into Excel's application object model, and that crossing carries real, measurable overhead per call. A direct Range loop pays that cost 50,000 times for a read-modify-write pattern, sometimes 100,000 times. Reading the whole block into a Variant array pays it exactly twice — once in, once out — and every operation in between happens entirely in memory, which VBA handles far faster than round-tripping through Excel.

Common Mistakes

• Reaching for the array pattern on a 50-row range where the difference is not perceptible, adding unnecessary complexity for no real gain.
• Forgetting a single-column range reads into a 2D array (rows, 1), not a 1D array — indexing it as one dimension raises a subscript error.
• Reading with .Value when the formulas themselves are needed — use .Formula if the array must preserve them.
• Not disabling Application.ScreenUpdating and Application.Calculation during a large Range loop, which compounds the slowness independently of the array question.

Related Guides on This Series

Apply this to Loop Through Range of Cells once your data grows past a few thousand rows, and pair large loops with proper error handling so a single bad row does not silently corrupt the whole array before it is written back.

Full reference: Microsoft Learn Timer function reference.

Frequently Asked Questions

Conclusion

Run the two timed subs above once on your own data to see the real gap, then default to the array pattern any time a loop approaches a few thousand rows. Pair large loops with On Error GoTo so a single bad value fails loudly instead of corrupting the whole array silently.

Prashant Panchal• ACA | FMVA® | 19 Years in Finance

Prashant Panchal is a Chartered Accountant (ACA) and Financial Modelling & Valuation Analyst (FMVA®) with 19 years of experience in finance, FP&A, and financial modelling across the GCC region. He is the founder of FinDataPro.