VBA Loop Through a Range: Examples and Best Practices

To loop through cells in Excel VBA, use For Each cell In worksheet.Range("A1:A10").Cells and place the action you want inside the loop. This visits each cell in the specified range without selecting it; use a numeric For loop when you need row or column numbers.

The basic For Each loop

A Range represents one cell or a collection of cells. In a For Each loop, VBA assigns each cell in turn to an object variable, commonly named cell. You can then read its value, inspect its address, or change a property. The range can be a fixed address, a named range, or a range held in a variable.

This example prints the address and value of each cell in A2:A20 on a worksheet named Data. Open the Visual Basic Editor’s Immediate window to see the output; in the editor, press Ctrl+G on Windows.

Option Explicit

Sub PrintRangeValues()
    Dim ws As Worksheet
    Dim cell As Range

    ' Save a copy of the workbook before running a macro.
    Set ws = ThisWorkbook.Worksheets("Data")

    For Each cell In ws.Range("A2:A20").Cells
        If Not IsError(cell.Value2) Then
            Debug.Print cell.Address(False, False), cell.Value2
        End If
    Next cell
End Sub

Next cell advances the loop to the next cell, and the loop ends after the last one in the range. The IsError check prevents a cell containing an Excel error such as #N/A from being treated as an ordinary value. If errors are meaningful to your task, handle them explicitly rather than silently skipping them.

Qualify the worksheet and workbook

A frequent source of VBA bugs is an unqualified range such as Range("A1:A10"). Without a worksheet qualifier, VBA uses the active sheet, which may not be the sheet you intended. Qualify the range with a worksheet variable, as in ws.Range("A1:A10"). The workbook is qualified too: ThisWorkbook means the workbook containing the VBA code, while ActiveWorkbook means the workbook currently active in Excel. They can differ.

For a macro stored in the same workbook as the data, ThisWorkbook.Worksheets("Data") is usually appropriate. If the macro is in Personal.xlsb or an add-in and should work on the active workbook, set the workbook variable deliberately instead. Worksheet names must match the sheet tab name, including spaces and punctuation. For code that should not fail when someone renames a tab, a worksheet’s VBA code name can be used, but that requires understanding the Project Explorer and the sheet’s properties in the VBA editor.

Use Set when assigning an object such as a worksheet or range to a VBA variable. For example, Set rng = ws.Range("A2:A20") stores a reference to the range; it does not copy the cell values.

Choose a loop for the task

For Each is convenient when the task is naturally about each cell: check whether it is blank, format a matching value, or inspect a formula. A numeric For loop is useful when the row number matters, when you need to compare neighboring rows, or when a second column must be read using the same row index.

Use For Each to examine each cell in a known range without needing a counter.
Use a numeric For loop to work with row numbers, a changing start and end, or related columns on the same row.
Use a Do loop only when the stopping condition is determined as the code runs; make sure the condition can eventually become true.

Here is a row-based example that finds the last used row in column A and checks each entry from row 2 down. It assumes row 1 contains a header. The test colors cells with negative numeric values in column B, while leaving errors and non-numeric entries alone.

Option Explicit

Sub HighlightNegativeValues()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim valueToCheck As Variant

    ' Save a copy of the workbook before running a macro.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For r = 2 To lastRow
        valueToCheck = ws.Cells(r, "B").Value2

        If Not IsError(valueToCheck) Then
            If IsNumeric(valueToCheck) Then
                If valueToCheck < 0 Then
                    ws.Cells(r, "B").Interior.Color = vbRed
                End If
            End If
        End If
    Next r
End Sub

The last-row expression starts at the bottom of column A and moves upward to the first non-empty cell. It works well when column A is populated for every record. If that column can contain gaps or the data extends farther in another column, determine the last row using a more suitable key column or a defined Excel Table instead. A loop from row 2 to row 1 does not process any data, so consider handling an empty sheet or header-only sheet if that is possible in your workflow.

Set a range once and handle blanks carefully

Assigning a range to a variable can make code easier to read and helps keep the target consistent. For example, set rng to ws.Range("A2:A100"), then use For Each cell In rng.Cells. The Cells property makes clear that the loop is over individual cells. It is also useful when a variable may refer to a range with more than one cell.

Blank cells need a decision based on the job. A cell can be truly empty, or it can contain a formula that returns an empty string. IsEmpty(cell.Value2) identifies an empty value, but does not treat a formula returning "" as empty. A test such as Len(CStr(cell.Value2)) = 0 can treat both as having no visible text, but convert only after considering errors and other data types. In particular, check IsError before converting or comparing a value that may be an Excel error.

VBA’s Value2 property is often a sensible choice for reading cell contents. It returns the underlying value without the special Currency and Date subtypes that Value can return. It does not change the number format shown in the worksheet; it affects how VBA receives the value. Use cell.Formula when you need the formula text rather than the calculated result.

Work with multiple areas and adjacent cells

A range can consist of separate areas, such as A1:A3,C1:C3. A For Each loop over that range’s cells visits the cells in all of its areas. This is handy when a selection is intentionally non-contiguous, but a single rectangular range is easier to reason about when possible. If you need to process each area as a separate block, loop over rng.Areas, then loop over the cells in each area.

When the operation depends on a cell’s position, a numeric loop can make the relationship clearer. For example, if a name is in column A and its status is in column B, use the same row number to read both. Avoid assuming that a neighboring cell is present or valid: check the relevant bounds and account for blank rows. If you need to compare a row with the one before it, start at the second data row so the first comparison does not refer to a header or an unintended cell.

For worksheets formatted as tables, Excel Tables provide a structured way to refer to data columns and automatically expand as rows are added. VBA can still loop through the table’s data body range, but be aware that an empty table may not have a data body range. A fixed address is simpler for a small, stable task; a table or dynamically determined range is generally more adaptable when the data changes over time.

Improve speed on large ranges

For a few dozen or a few hundred cells, a straightforward cell-by-cell loop is usually easy to maintain. On a large range, repeated reads and writes between VBA and the worksheet can be slow. A common improvement is to read a block into a VBA array, perform calculations in memory, and write a block back in one operation. This is most useful when every cell in a contiguous rectangular range can be handled together; separate areas and one-cell ranges need additional care.

Another option for some tasks is to use Excel’s built-in worksheet features rather than a macro. Filtering, conditional formatting, and worksheet formulas can be clearer and update automatically as data changes. A VBA loop is most appropriate when the process involves custom logic, repeated actions, or a workflow that is awkward to express with built-in tools.

Excel VBA can also change application settings such as screen updating or calculation mode to reduce overhead in a long macro. These settings should be restored even if an error occurs; otherwise Excel may be left in an unexpected state. Do not add such optimizations automatically to a short loop. First identify the bottleneck, then use a structured error-handling path that restores the original settings.

Common errors and safer habits

Most loop problems come from a mismatch between the range and the assumptions in the code, rather than from the loop syntax itself. Check the sheet name, workbook, first data row, and last-row column before running a macro. If the code changes values or formatting, test it on a copy and, where practical, on a small sample first.

Wrong sheet: qualify every range with the intended worksheet rather than relying on whichever sheet is active.
Wrong last row: choose a column that reliably identifies records, or use a table’s data range.
Error values: test with IsError before numeric comparisons, string conversion, or arithmetic.
Unexpected blanks: distinguish genuinely empty cells from formulas that display an empty string.
Slow execution: avoid selecting or activating each cell; work directly with range objects and consider arrays for large blocks.

Macros that alter a workbook cannot be reversed with Excel’s Ctrl+Z undo command. Save a separate copy before running one, especially when it edits or deletes data. Use Option Explicit at the top of a module so VBA requires variables to be declared; this helps catch misspelled variable names before they cause confusing results.

Run VBA in Excel and know the limits

These examples are for desktop Excel for Microsoft 365 on Windows. To add one, open the workbook, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. Save the workbook in a macro-enabled format such as .xlsm if it needs to retain VBA. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use desktop Excel for those tasks.

Excel for Mac also includes VBA support, but Windows-only features, external libraries, and some automation methods may not work the same way. The editor shortcut can depend on the keyboard and macOS settings; on many Mac keyboards use Option+F11, or Fn+Option+F11 when the function keys control hardware features. If that shortcut is unavailable, open the Visual Basic Editor from Excel’s Developer tab. The examples here use worksheet and range operations rather than Windows-specific APIs.

Only enable macros from workbooks and sources you trust. Excel may block macros based on the file’s origin or security settings. Do not weaken security settings just to run an unfamiliar file; ask the file’s provider for a trusted, appropriate version or use a non-macro alternative.

Frequently Asked Questions

How do I loop through every used cell in a worksheet?
Use the worksheet’s UsedRange as the loop range, for example For Each cell In ws.UsedRange.Cells. UsedRange can include cells that were formatted or previously used, so it may be larger than the visible data. For a predictable dataset, a table or a clearly defined range may be a better target.

Can I use For Each with a range variable?
Yes. Declare the variable as Range, assign it with Set rng = ws.Range("A1:A10"), and loop with For Each cell In rng.Cells. This keeps the range definition separate from the actions performed on its cells.

How do I skip blank cells in a VBA loop?
Decide whether formulas returning an empty string should count as blank. For truly empty cells, test IsEmpty(cell.Value2); for cells with no displayed text, a length check may fit better. Check for errors before converting the value to text.

Why is my VBA loop processing the wrong worksheet?
An unqualified reference such as Range("A1:A10") acts on the active sheet. Assign the intended worksheet to a variable and use ws.Range("A1:A10") throughout the macro. Also confirm whether you mean ThisWorkbook or ActiveWorkbook.

Can VBA loops run in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use VBA; for browser-based automation, consider Office Scripts if they meet the task’s requirements.

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.