📎 This article includes 1 downloadable practice file ↓

Here is a macro I see in almost every workbook I am asked to “speed up”:

For r = 2 To 50000
    If Cells(r, 5).Value > 100000 Then Cells(r, 6).Value = "Large"
Next r

It works. It also takes 20–40 seconds on 50,000 rows, because every Cells(r, 5) is a round trip between VBA and Excel. Each trip is cheap. Fifty thousand of them are not.

Arrays fix that. You pull the whole block into memory in one go, loop over it there (which is extremely fast), and write the answers back in one go. Same logic, usually under a second. This lesson is the single biggest speed-up you will learn in VBA.

What an array is

An array is a variable that holds many values under one name, each with a numbered position. Think of it as a column of cells that lives inside VBA instead of on a sheet.

Dim months(1 To 12) As String
months(1) = "Jan"
months(2) = "Feb"
Debug.Print months(2)      ' Feb

(1 To 12) sets the first and last positions. If you write Dim x(12) instead, VBA starts counting at 0 and you get 13 slots, 0 to 12 — a classic source of off-by-one bugs. I always write the range explicitly.

The big trick: a range straight into an array

Dim data As Variant
data = Range("A2:F50001").Value      ' one trip: 50,000 rows × 6 columns
Debug.Print data(1, 5)               ' row 1, column 5 of the block (= cell E2)

Three things surprise people here:

• The variable must be a Variant, not Dim data() As String.
• You always get a 2-D array (rows, columns), even for a single column.
• It always starts at 1, not 0, whatever your sheet layout.

The fast version of the macro

Sub FlagLargeOrders()
    Dim ws As Worksheet, data As Variant, out() As Variant
    Dim r As Long, lastRow As Long
    Set ws = ThisWorkbook.Worksheets("Orders")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    data = ws.Range("E2:E" & lastRow).Value          ' read once
    ReDim out(1 To UBound(data, 1), 1 To 1)           ' same height, one column

    For r = 1 To UBound(data, 1)                      ' loop in memory
        If data(r, 1) > 100000 Then out(r, 1) = "Large" Else out(r, 1) = ""
    Next r

    ws.Range("F2").Resize(UBound(out, 1), 1).Value = out   ' write once
End Sub

On my laptop the slow version took 31 seconds for 50,000 rows. This one took 0.2 seconds. The practice workbook has both macros and a timer, so you can see the difference on your own machine.

Dynamic arrays and ReDim

When you don’t know the size in advance — say, collecting every invoice above a limit — declare the array without a size and set it later with ReDim:

Dim big() As String, n As Long
ReDim big(1 To 1000)                 ' room for 1,000
For r = 1 To UBound(data, 1)
    If data(r, 1) > 100000 Then
        n = n + 1
        big(n) = "Row " & r + 1
    End If
Next r
If n > 0 Then ReDim Preserve big(1 To n)   ' trim to what we used

ReDim on its own wipes the contents; ReDim Preserve keeps them but can only change the last dimension. Resizing inside the loop on every row works but is slow — reserve generously, then trim once at the end, as above.

Split and Join: arrays from text

Dim parts As Variant
parts = Split("North,South,East,West", ",")
Debug.Print parts(0)                ' North  (Split starts at 0!)
Debug.Print UBound(parts)           ' 3
Debug.Print Join(parts, " | ")      ' North | South | East | West

Note the inconsistency: arrays from Split start at 0, arrays from ranges start at 1. Using LBound and UBound in your loops instead of hard-coded numbers saves you from ever caring.

Where people go wrong

Practice

The workbook below has an Orders sheet with 50,000 rows and a Macros sheet with buttons for: the slow loop, the array version (with a timer), collecting large orders into a dynamic array, and a Split/Join example. Run the slow one first so you feel the difference.

Next lesson: writing your own Subs and Functions, so code like this becomes reusable instead of copy-pasted.