VBA Loop Over a Range: Methods, Examples, and Tips

To loop over cells in an Excel range with VBA, use For Each cell In someRange when you need to inspect or change each cell, or a numbered For loop when you need row or column positions. For example, For Each cell In Worksheets("Data").Range("B2:B20") visits the cells in that range one at a time.

What a range loop does

A VBA range is an Excel object that can represent one cell, a rectangular block of cells, or multiple areas. A loop repeats a set of instructions for the cells or positions you specify. This is useful for tasks such as checking values, formatting qualifying cells, copying results, or applying a rule that is awkward to express as a worksheet formula.

In a For Each loop, the loop variable should be declared as a Range. Each pass assigns one cell from the range to that variable, so you can refer to its value, address, or formatting. The following example colors nonempty cells in a fixed range. Before running a macro, save a copy of the workbook: VBA changes generally cannot be reversed with Ctrl+Z.

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

    ' Set the worksheet explicitly so the macro uses the intended sheet.
    Set ws = ThisWorkbook.Worksheets("Data")

    For Each cell In ws.Range("B2:B20")
        If Len(cell.Value2) > 0 Then
            cell.Interior.Color = RGB(255, 255, 0)
        End If
    Next cell
End Sub

The macro assumes a worksheet named Data exists in the workbook containing the code. Change that name and the address to fit your file. Value2 reads the cell value without applying VBA’s Currency or Date conversions; it is commonly used when a macro needs the underlying value rather than a formatted display.

Using For Each for a range

For Each is usually the clearest choice when the operation applies independently to every cell in a known range. The loop does not require you to calculate a row number just to get to the next cell. This also makes the code easier to adapt from one column to a rectangular block.

For example, this macro checks a status column and writes a note in the adjacent column for rows marked Ready. It finds the last used row in column C, then processes only rows 2 through that row. Save a copy before running it, especially if the adjacent column contains existing data.

Sub MarkReadyRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range

    ' Use the Data sheet and locate the last nonempty cell in column C.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    If lastRow < 2 Then Exit Sub

    For Each cell In ws.Range("C2:C" & lastRow)
        If StrComp(CStr(cell.Value2), "Ready", vbTextCompare) = 0 Then
            cell.Offset(0, 1).Value = "Review"
        End If
    Next cell
End Sub

Offset(0, 1) means the cell on the same row, one column to the right. StrComp with vbTextCompare compares text without treating uppercase and lowercase as different. The example’s final-row method is useful for a simple, consistently populated column, but it finds the last nonempty cell in that column only. If column C has gaps at the bottom while other columns contain records, choose a more suitable column or a different way to determine the data boundary.

When an indexed For loop is better

Use an indexed loop when the row or column number is part of the task—for example, when comparing values across several columns on the same row, calculating a row-specific destination, or counting records. The loop counter is a number, so use Cells(rowNumber, columnNumber) to address a cell. In this example, column C contains statuses and column D receives a result.

Save a workbook copy before running this macro because it writes into column D. The sheet is qualified through ws, and the row counter is explicitly declared as a Long, which is appropriate for Excel row numbers.

Sub LabelReadyRowsByIndex()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim statusValue As Variant

    ' Read status values from the Data sheet and write labels in column D.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row

    For r = 2 To lastRow
        statusValue = ws.Cells(r, 3).Value2
        If Not IsError(statusValue) Then
            If StrComp(CStr(statusValue), "Ready", vbTextCompare) = 0 Then
                ws.Cells(r, 4).Value = "Review"
            End If
        End If
    Next r
End Sub

Here, Cells(r, 3) means column C on row r, while Cells(r, 4) means column D on that row. Storing the value in a Variant lets the code check for an Excel error value before converting it to text. Without that check, a cell containing an error such as #N/A can cause a type mismatch during a text comparison.

Looping through rows and columns

To process a rectangular range, a nested loop can make the row and column positions explicit. The outer loop handles rows and the inner loop handles columns. This is useful when each row represents one record and each column contains a field you need to inspect. Set the start and end positions to match the actual data; this example checks A1 through C5.

Before running any macro that changes cells, save a copy of the workbook. This example only counts numeric values, so it does not alter the worksheet.

Sub CountNumbersInBlock()
    Dim ws As Worksheet
    Dim r As Long
    Dim c As Long
    Dim numberCount As Long
    Dim valueToCheck As Variant

    ' Count numeric cells in a three-column block.
    Set ws = ThisWorkbook.Worksheets("Data")

    For r = 1 To 5
        For c = 1 To 3
            valueToCheck = ws.Cells(r, c).Value2
            If Not IsError(valueToCheck) Then
                If IsNumeric(valueToCheck) And Len(valueToCheck) > 0 Then
                    numberCount = numberCount + 1
                End If
            End If
        Next c
    Next r

    MsgBox numberCount
End Sub

In VBA, And should not be treated as a short-circuit operator: both conditions may be evaluated. The example checks for an error first in a separate If, then checks whether the value is numeric and nonempty. This avoids asking IsNumeric to interpret an Excel error value.

Speed: cell-by-cell versus arrays

For a small range, a cell-by-cell loop is normally easier to read and is often fast enough. For a large block, repeatedly reading from and writing to worksheet cells can be slow because the macro crosses between VBA and Excel for each operation. A common improvement is to read a range into a VBA array, process the values in memory, and—if needed—write a result array back in one operation.

This example reads statuses into memory and counts the exact Ready matches without changing cells. Save a copy before running macros as a general precaution, even though this one only reads data.

Sub CountReadyInArray()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim data As Variant
    Dim i As Long
    Dim readyCount As Long

    ' Read the status column into memory before checking its values.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    If lastRow < 2 Then Exit Sub

    data = ws.Range("C2:C" & lastRow).Value2

    For i = 1 To UBound(data, 1)
        If Not IsError(data(i, 1)) Then
            If StrComp(CStr(data(i, 1)), "Ready", vbTextCompare) = 0 Then
                readyCount = readyCount + 1
            End If
        End If
    Next i

    MsgBox readyCount
End Sub

A one-cell range returns a single value rather than a two-dimensional array, so code that calls UBound(data, 1) should account for that possibility if the selected range might contain only one cell. This particular example starts at row 2 and exits when there are no data rows, but a dataset with exactly one record still produces a one-cell range. For fully general code, handle that case separately or use a range guaranteed to contain multiple cells.

Arrays are not automatically safer or better. If you write a modified array back over an input range, you replace the cells’ contents, including formulas in that range. Keep input and output ranges separate when formulas must be preserved, and verify that the array’s dimensions match the destination range.

Choosing the range and worksheet safely

Always make it clear which worksheet a range belongs to. An unqualified reference such as Range("A1:A10") refers to the active sheet, which may not be the sheet the user expects. Qualifying it as ws.Range("A1:A10") makes the target explicit. Likewise, ThisWorkbook means the workbook containing the VBA code; ActiveWorkbook means the workbook currently active in Excel, which may be different.

Choose a range boundary that matches the data structure:

For a fixed template, use a fixed address such as B2:B20.
For a simple list, find the last used row in a key column with Cells(Rows.Count, column).End(xlUp).Row, qualifying Rows.Count through the worksheet as in the examples.
For a formatted Excel table, consider its ListObject and DataBodyRange, which represent the table’s data rows and can adapt as table rows are added or removed.
For separate blocks, define each area deliberately; a multi-area range may not behave like one continuous rectangle.

When a table has no data rows, its DataBodyRange can be Nothing. Check for that before looping. Also decide whether the header row belongs in the operation; a loop over the whole table range may include headers, while its data-body range excludes them.

Common problems and safeguards

Range loops are simple, but a few common details can change their behavior or cause errors. Review these points before adapting a sample to an important workbook:

Blank cells: Decide whether blanks should be skipped, counted, or treated as zero. For text checks, Len(cell.Value2) > 0 treats a formula returning an empty string differently from a truly empty cell only in some operations; test the behavior you need.
Error values: Test with IsError before converting a cell value to text or a number. Error values are not ordinary strings.
Filtered or hidden rows: A loop through a normal range generally includes hidden and filtered-out cells. If only visible cells should be processed, explicitly obtain the visible cells and account for the possibility that the result has multiple areas.
Changing the range during the loop: Deleting rows or changing cells that determine the loop boundary can make an indexed loop skip records or process an unexpected set. Consider collecting target row numbers first or iterating from the bottom upward when deleting rows.
Events and calculation: Writing many cells can trigger worksheet events or recalculation. If a macro temporarily changes application settings such as events or calculation, restore their original values even if an error occurs.
Macro recovery: Excel’s undo history does not provide a normal Ctrl+Z undo for VBA actions. Use a saved copy, test on sample data, and make the macro’s write range explicit.

If code does not run, check the worksheet name, range address, and variable declarations first. In Excel for Microsoft 365 on Windows, open the VBA editor from Developer > Visual Basic; if the Developer tab is not visible, enable it in Excel Options. On Mac, use Developer > Visual Basic when the Developer tab is enabled; keyboard shortcuts can vary with the keyboard and function-key settings. A workbook containing macros should be saved in a macro-enabled format such as .xlsm.

Frequently Asked Questions

How do I loop through every cell in a selected range?
Use a For Each loop with a Range variable, such as For Each cell In Selection. For a repeatable macro, a specifically named worksheet and range are usually safer than relying on whatever selection happens to be active.

Is For Each or For better for looping through cells?
Use For Each when you need to handle each cell and do not need its row number. Use an indexed For loop when the row or column position matters, or when you need to coordinate values from multiple columns on each row.

How do I loop to the last row with data?
For a list with a reliable key column, use Cells(Rows.Count, column).End(xlUp).Row, with both the worksheet and row count properly qualified. This finds the last nonempty cell in that column, not necessarily the last used row anywhere on the sheet.

Does a VBA loop include hidden or filtered-out rows?
A loop over a standard range normally includes those cells. If the task should affect visible cells only, select the visible cells explicitly and account for multiple disjoint areas rather than assuming the visible result is one continuous block.

Can I undo a VBA loop with Ctrl+Z?
No. A macro’s changes generally cannot be undone through Excel’s regular undo command, so save a copy and test the macro on a duplicate or sample range before running it on important data.

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.