VBA Loop Through Cells: Methods and Examples

To loop through cells in Excel VBA, use For Each for a specified range or a counter-based For loop when you need row and column numbers. For example, For Each cell In ws.Range("A1:C10").Cells visits every cell in that range; the examples below show how to read, test, and change those cells safely.

What a cell loop does

A VBA loop repeats instructions for a sequence of cells. On each pass, your code can examine a value, apply a condition, write a result, or perform another task. The loop itself does not determine which cells are included: the range you give it does. That makes choosing the range deliberately just as important as choosing the loop structure.

In Excel for Microsoft 365 on Windows, macros are written and run in the Visual Basic for Applications editor. Press Alt+F11 to open it, then use Insert > Module to add a standard module and paste a procedure there. On Mac, the VBA editor is available in Excel, but the keyboard shortcut and editor controls can differ; use Tools > Macro > Visual Basic Editor if the shortcut is unavailable. VBA macros do not run in Excel for the web. Save a copy of the workbook before running a macro: macro changes cannot be undone with Ctrl+Z.

Use For Each for a fixed range

For Each is usually the clearest choice when the task is simply to visit every cell in a range. The loop variable must be an object variable, conventionally declared as Range. This sample prints the address and value of each nonempty cell in A1:C10 to the Immediate window, which you can display in the editor with Ctrl+G on Windows.

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

    ' Read cells from the named worksheet, not the active sheet.
    Set ws = ThisWorkbook.Worksheets("Data")

    For Each cell In ws.Range("A1:C10").Cells
        If Not IsError(cell.Value) Then
            If Len(CStr(cell.Value2)) > 0 Then
                Debug.Print cell.Address(False, False), cell.Value2
            End If
        Else
            Debug.Print cell.Address(False, False), "Excel error"
        End If
    Next cell
End Sub

Change Data and A1:C10 to match your workbook. The .Cells property makes explicit that the loop is over individual cells, including when the range contains multiple rows or columns. Value2 reads the underlying value without the Currency and Date subtypes that Value can return. The error check matters because applying a string conversion to an Excel error value can raise a VBA type mismatch.

For a simple task, the body can be shorter. For example, to make every cell in a selected range uppercase, use cell.Value = UCase$(CStr(cell.Value)) only after deciding how blanks, errors, numbers, and formulas should be handled. A cell loop can overwrite formulas with values, so do not use a transformation blindly on a mixed range.

Loop through rows and columns by index

Use a counter-based loop when your logic depends on the row number, column number, or position in a range. A common pattern finds the last used row in a key column and processes rows beneath a header. This example checks column B and writes a status in C when B is blank.

Sub MarkMissingValues()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNum As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    For rowNum = 2 To lastRow
        If Len(CStr(ws.Cells(rowNum, "B").Value2)) = 0 Then
            ws.Cells(rowNum, "C").Value = "Missing"
        End If
    Next rowNum
End Sub

The search starts at the bottom of column B and uses End(xlUp), similar to pressing Ctrl+Up from the bottom of that column. If B1 is the heading and the column has no data below it, lastRow is 1, so the loop from row 2 to row 1 does not run. If another column defines which records count as present, use that column instead. If records can have blanks in the chosen key column, this method may stop too early.

To walk a rectangular block by row and column, use nested loops:

Sub VisitMatrix()
    Dim ws As Worksheet
    Dim rowNum As Long
    Dim colNum As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    For rowNum = 2 To 20
        For colNum = 1 To 4
            If ws.Cells(rowNum, colNum).Value2 = "Review" Then
                ws.Cells(rowNum, colNum).Interior.Color = vbYellow
            End If
        Next colNum
    Next rowNum
End Sub

Here, the outer loop chooses a row and the inner loop visits four columns in that row. The Cells(rowNum, colNum) syntax uses numeric row and column indexes, with column 1 meaning A. Nested loops are useful for grids, but for one contiguous range a single For Each loop is usually easier to read.

Choose the range before you start

There is no universally correct way to define the cells to process. Use a fixed address when the input area is stable, a last-row calculation when a list grows, and UsedRange only when the worksheet’s used area is genuinely the intended scope.

Fixed range: ws.Range("A2:A100") is predictable and easy to review. It may include empty cells or miss new records if the list changes.
Last row in a key column: ws.Cells(ws.Rows.Count, "A").End(xlUp).Row adapts to a growing list, provided column A is reliably populated for each record.
UsedRange: ws.UsedRange covers the worksheet’s reported used area, which can include cells that were formatted or edited in the past. It may be larger than the visible data.
Excel table: A table’s DataBodyRange can target its data rows without the header. Check that it is not Nothing when the table has no data rows.

Qualify ranges with a worksheet variable, as in ws.Range("A1:A10"). An unqualified Range("A1:A10") refers to the active sheet, which may not be the sheet you intended if a user or another macro changes the active worksheet. Similarly, ThisWorkbook means the workbook containing the macro, while ActiveWorkbook means the workbook currently active in Excel. Choose based on where the data actually resides.

Read and change cells without selecting them

VBA does not need to select or activate a cell before reading or changing it. Direct references such as ws.Cells(rowNum, 2).Value are clearer and less fragile than selecting a worksheet and then using Selection. Selection-dependent code can fail or act on the wrong sheet when the user has another workbook open.

When changing cells, decide whether formulas should be preserved. Assigning to a cell’s Value replaces its formula if it contains one. To inspect formulas, use HasFormula or Formula; to copy a formula, assign a formula deliberately rather than treating the cell like ordinary text. Also consider whether formatting should be changed: setting Interior.Color modifies the cell’s fill, while writing to Value changes its content.

If the task is to process many thousands of cells, repeatedly reading and writing individual cells can be slow. A common faster approach is to read a rectangular range into a Variant array, process the array in memory, and write it back in one operation. That approach needs care: a one-cell range may return a scalar rather than a two-dimensional array, and writing a full array back can replace formulas or affect cells you did not mean to change. For modest ranges, a straightforward cell loop is often the better tradeoff for clarity.

Handle blanks, errors, and loop control

Cells can contain a truly empty value, a formula that returns an empty string, an Excel error such as #N/A, or a value that only looks blank because of spaces. These cases are not always equivalent. IsEmpty(cell.Value) tests whether a cell has no value, but it does not treat a formula returning "" as empty. Len(CStr(cell.Value2)) = 0 can identify an empty string, while text containing spaces needs trimming if spaces should count as blank. Check IsError before converting or comparing a value that could be an error.

Use Exit For when the desired item has been found and there is no reason to keep searching. With nested loops, it exits only the innermost loop; add a flag or restructure the code if you need to leave both loops. A loop that modifies its own range needs extra thought. For example, deleting rows while counting upward can skip the row that shifts into the deleted row’s position. In that situation, loop from the bottom upward, or collect the rows to remove and delete them after inspection.

For a loop that may encounter unexpected values, add an explicit condition and a clear response rather than relying on broad error suppression. On Error Resume Next can hide unrelated problems and leave a macro appearing to succeed when it did not. If error handling is necessary, handle the specific operation and restore normal error handling with On Error GoTo 0.

Common mistakes and performance choices

Using the wrong worksheet: Unqualified Cells and Range references use the active sheet. Set a Worksheet variable and qualify every reference.
Using an Integer for row numbers: Excel worksheets have more rows than a VBA Integer can represent. Declare row counters and last-row variables as Long.
Assuming UsedRange means data only: Old formatting can enlarge it. Inspect the actual range or use a known key column to determine the list boundary.
Overwriting formulas: Assigning a new value replaces a formula. Confirm the target cells and keep a backup before running a write operation.
Looping one cell at a time for a huge task: For large data sets, array processing can reduce worksheet communication, but verify how formulas, errors, and blank cells should be treated before writing results back.

Do not optimize before the correct range and result are clear. A short loop is often fast enough for a few hundred or a few thousand cells. For larger jobs, avoid screen updates or recalculation changes unless you know how to restore the prior application settings if the macro stops. A macro should leave Excel in the same usable state in which it found it.

When a loop is not the best tool

VBA is useful when processing requires custom decisions, repeated actions, or interaction with other workbook objects. But a cell-by-cell loop is not always needed. Worksheet formulas can recalculate automatically and show their logic directly in the sheet. Excel’s built-in filter, sort, and Find commands may be simpler for one-off tasks. Power Query is often a better fit for repeatable import and cleanup workflows, especially when the work is mainly transforming tabular data rather than applying unique cell-level rules.

A useful test is whether the operation can be expressed as one worksheet operation. If every value in a range needs the same straightforward calculation, a formula filled down may be easier to audit than a macro. If each row requires conditional actions or the task must be repeated with one command, VBA may be appropriate. Choose based on maintainability as well as speed.

Frequently Asked Questions

How do I loop through every used cell in a worksheet?
Set a worksheet variable, then use For Each cell In ws.UsedRange.Cells. Remember that UsedRange can include cells with leftover formatting, so it may cover more than the current data.

How do I loop through cells until the last row?
Find the last row in a reliably populated column with ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, then loop from the first data row to that result. Adjust the column and starting row to match your layout.

Is For Each faster than a For loop?
For ordinary worksheet ranges, choose the form that makes the logic clearest; performance depends more on what happens inside the loop and how often VBA reads or writes worksheet cells. For very large ranges, reading and processing values in an array may be faster.

How do I skip blank cells in a loop?
Test each cell before processing it. Use IsEmpty for genuinely empty cells, or check the value’s length if formulas returning an empty string should also be skipped. Check for errors first when the range may contain Excel error values.

Can I run a VBA macro in Excel for the web?
No. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Open the workbook in the desktop Excel application to run VBA.

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.