Excel VBA Fast Loop: Speed Up Repetitive Macro Code

VBA & macros · Updated November 21, 2026

To make an Excel VBA loop fast, avoid reading or writing worksheet cells one at a time. Read the needed range into a Variant array, process the values in memory, and write the result back in one operation; also avoid unnecessary recalculation, screen updates, and event handling while the macro runs.

Why some VBA loops are slow

A VBA loop that performs simple arithmetic or text work in memory is usually not the main bottleneck. The bigger cost often comes from repeatedly communicating between VBA and the Excel worksheet. Each statement that gets or sets a cell value can require Excel to resolve a workbook, worksheet, row, and column reference. Doing that thousands of times can make a macro much slower than doing the same work on an array held in memory.

Other common sources of delay include recalculating formulas after each change, repainting the Excel window, running worksheet event procedures, and repeatedly searching for the same range boundary. A loop can also appear slow because it processes more cells than intended, for example by using an inflated last row or looping through every cell in a large worksheet when only one column matters.

The right optimization depends on what the macro actually does. If the task is simply to format a block or clear contents, one range operation may be better than any loop. If each row needs a decision based on its values, reading the rows into an array and looping over that array is often a practical approach.

Use an array for row-by-row work

This example reads text from column A on a worksheet named Data, converts nonblank, non-error values to uppercase in memory, and writes the results to column B. It assumes headers are in row 1 and data begins in row 2. Change the worksheet name and columns to suit the workbook. The macro replaces existing values in the corresponding cells in column B, so save a copy of the workbook before running it: macros cannot be undone with Ctrl+Z.

Option Explicit

Sub UppercaseColumnFast()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim data As Variant
    Dim i As Long
    Dim oldScreen As Boolean
    Dim oldEvents As Boolean
    Dim oldCalc As XlCalculation
    Dim errorText As String

    ' Keep the current Excel settings so they can be restored.
    oldScreen = Application.ScreenUpdating
    oldEvents = Application.EnableEvents
    oldCalc = Application.Calculation

    On Error GoTo Failed

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then GoTo Finish

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    ' Transfer the range to memory with one read.
    data = ws.Range("A2:A" & lastRow).Value2

    For i = 1 To UBound(data, 1)
        If Not IsError(data(i, 1)) Then
            If Not IsEmpty(data(i, 1)) Then
                data(i, 1) = UCase$(CStr(data(i, 1)))
            End If
        End If
    Next i

    ' Transfer the processed values back with one write.
    ws.Range("B2:B" & lastRow).Value2 = data

Finish:
    Application.ScreenUpdating = oldScreen
    Application.EnableEvents = oldEvents
    Application.Calculation = oldCalc
    If Len(errorText) > 0 Then MsgBox errorText, vbExclamation, "Macro stopped"
    Exit Sub

Failed:
    errorText = Err.Description
    Resume Finish
End Sub

The important change is that the loop accesses data(i, 1), not a worksheet cell. The range is read once and written once, reducing worksheet communication. The array is two-dimensional even though the source range has one column, so its row values use a second index of 1. If the range has only one cell, Excel still returns a scalar rather than a two-dimensional array; this example avoids that edge case by exiting when there are no data rows and expecting at least one data row otherwise.

Choose the simplest operation that fits

Do not add a VBA loop when Excel can perform the whole task on a range directly. For example, assigning a constant to a rectangular range, clearing a range, or applying a number format can often be done with one statement. A formula can also be placed in a range in one operation and, if needed, converted to values afterward. These approaches avoid per-cell VBA work entirely.

Use an array loop when the result depends on row-specific logic that is awkward to express as a worksheet formula or a single range operation. Typical examples include checking several fields, applying custom text rules, or building a new output table. For data that must be read and then selectively changed, arrays combine flexible logic with fewer transfers to the worksheet.

  • One uniform change: apply a range-level property or method rather than looping through every cell.
  • Formula-based calculation: consider writing a formula to a range, then calculate or convert the results if appropriate.
  • Different decision for each row: read the relevant columns into arrays, process them in VBA, and write output in a batch.

Manage calculation, screen updates, and events carefully

For a macro that makes many changes, temporarily setting Application.ScreenUpdating to False can prevent Excel from repeatedly repainting the window. Setting Application.EnableEvents to False prevents event procedures, such as a worksheet change handler, from running for each edit. Setting Application.Calculation to xlCalculationManual can avoid repeated formula recalculation while a large batch of values is being changed.

These settings affect Excel beyond the current loop, which is why the example stores their original values and restores them on both normal completion and an error. Do not simply set calculation back to automatic: a user may have had manual calculation selected before the macro started. Likewise, leaving events disabled can make other workbook features appear broken. Manual calculation can leave formula results temporarily out of date, so understand the workbook’s calculation needs before using it. Restoring the prior setting does not guarantee that every dependent formula has been recalculated in the exact way a particular workbook requires; add an explicit calculation step only when the task calls for it.

These application-wide switches are not a substitute for batching worksheet operations. They may help in a macro that performs many visible changes, but the largest improvement often comes from replacing repeated cell reads and writes with a bulk transfer.

Keep the loop bounds and data handling accurate

Finding the last row once, before the loop, is usually better than asking Excel for the boundary on every iteration. The example uses End(xlUp) from the bottom of column A, which finds the last nonempty cell in that column. It is appropriate when column A is the column that defines which records are present. If column A can contain gaps or be blank for valid records, use a more suitable key column or a different boundary rule.

Also read only the columns needed for the task. Loading an entire worksheet into an array when the macro needs two fields uses more memory and can make the code harder to maintain. If several columns are needed, read one rectangular block and refer to the appropriate second-dimension index, or load separate ranges when that better reflects the data layout.

Value2 transfers cell values without the Currency and Date subtypes that the Value property can return. That can be useful for predictable bulk processing, but date values are then represented as Excel serial numbers. Preserve or format dates appropriately when writing results. The uppercase example is intended for text; it converts numeric values to text if they occur in the input column, so remove or change that conversion if the column contains numbers that must remain numeric.

Measure changes instead of guessing

Test the macro on a representative copy of the workbook and compare the same task before and after a change. Keep the row count, formulas, workbook state, and output consistent; otherwise, the timing comparison may not mean much. Excel’s VBA Timer function returns the number of seconds since midnight, so a simple elapsed-time measurement should account for a run that crosses midnight. It is also not a high-precision benchmarking tool for extremely short operations.

For a useful comparison, time the whole operation that matters, including the range read and write, rather than timing only the in-memory loop. A very quick loop does not help much if a later step is the bottleneck. Run a few comparisons on the same data and avoid claiming a universal speed multiplier: results vary with workbook size, formulas, Excel version, computer, and the work being performed.

Common problems and practical fixes

  • Excel is still slow after adding an array: check whether formulas, event macros, external links, or another step dominates the run time. The loop may not be the bottleneck.
  • Results shift or overwrite the wrong cells: match the input and output range dimensions, and confirm that the array’s first element corresponds to the first worksheet row.
  • Blank cells or errors cause a failure: test values with IsEmpty and IsError before converting or manipulating them. Decide deliberately whether to preserve, replace, or report error values.
  • Some workbook behavior stops: ensure events and calculation settings are restored even if the macro encounters an error. An error-handling path is essential when changing application-wide settings.
  • The macro processes too many rows: verify that the chosen last-row column actually represents the records to process, and avoid relying blindly on the worksheet’s UsedRange, which may include cells that were formatted or edited in the past.

Frequently Asked Questions

Is an array always faster than a VBA cell loop? Not in every situation, but bulk reads and writes often help when many cells are involved because they reduce interaction with the worksheet. For a tiny range, the difference may be negligible; measure the complete task.

Should I use For Each or For for a fast loop? Either can be appropriate. The more important choice is whether each iteration accesses worksheet cells or values already in memory. Use the loop form that makes the logic clear, then avoid unnecessary worksheet interaction.

Does turning off ScreenUpdating make every macro faster? No. It can reduce display work during many changes, but it does not fix inefficient cell-by-cell data transfer or slow calculations. Restore its original value when the macro ends.

Why use Value2 instead of Value? Value2 avoids returning Currency and Date subtypes, which can make bulk value handling more predictable. Date values are serial numbers with Value2, so account for that when interpreting or formatting them.

Can I undo a VBA macro with Ctrl+Z? Generally, no. Running a macro clears Excel’s normal undo history, and Ctrl+Z cannot reverse its changes. Save a separate copy before running code that changes workbook data.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.