Excel VBA Loop Through Cells in a Range

VBA & macros · Updated November 22, 2026

To loop through cells in a range with Excel VBA, use a For Each loop: For Each cell In Range("A1:A10") … Next cell. This is the simplest and most readable method, and it works for any contiguous or non-contiguous range you reference.

Why You Need a Loop Over a Range

Excel VBA is built around the object model: Workbooks contain Worksheets, Worksheets contain Ranges, and Ranges contain Cells. When you need to perform the same operation on many cells—clearing values, applying formatting, calculating a sum, or checking a condition—you could write one line per cell, but that’s impractical for more than a handful. A loop lets you write the logic once and let VBA repeat it for every cell in the range you specify.

Loops are also essential when the number of cells isn’t known in advance. For example, you might need to process all rows that contain data, and the row count changes each time the macro runs. A loop that checks the current cell and stops when it hits an empty one handles that dynamic situation gracefully.

The Core Syntax: For Each vs. For Next

There are two primary loop structures in VBA for iterating over a range. The For Each loop is the most direct:

Dim cell As Range
For Each cell In Range("A1:A10")
    cell.Value = cell.Value * 2
Next cell

This multiplies each value in A1:A10 by 2. The variable cell is declared as a Range object, and on each iteration it refers to the next cell in the range.

The alternative is a For loop with an index, which gives you more control over the order and lets you use the row/column numbers directly:

Dim i As Long
For i = 1 To 10
    Cells(i, 1).Value = Cells(i, 1).Value * 2
Next i

Here Cells(i, 1) refers to row i, column 1 (column A). The For loop is useful when you need to skip cells or when the range is based on a calculated start and end row.

How to Reference the Range Correctly

You can reference a range in several ways, and the choice affects how your loop behaves. The most common are:

  • Direct address: Range("B2:D10") – simple and readable, but hard-coded.
  • Using Cells with row/column numbers: Range(Cells(2, 2), Cells(10, 4)) – useful when row/column counts are variables.
  • Named range: Range("MyData") – if you’ve defined a named range in the workbook, it’s more maintainable.
  • CurrentRegion: Range("A1").CurrentRegion – expands to the contiguous block of data around A1, handy for dynamic data sets.

If you’re looping through a column of unknown length, you can find the last used row with Cells(Rows.Count, 1).End(xlUp).Row. For example:

Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Dim cell As Range
For Each cell In Range("A1:A" & lastRow)
    ' process each cell
Next cell

This works on any worksheet and avoids looping through thousands of empty cells.

Practical Example: Summing Values and Highlighting Blanks

Let’s combine a loop with a condition. Suppose you have numbers in A1:A20 and you want to sum only the positive values, and also highlight any blank cells in that range. Here’s a complete macro:

Sub ProcessRange()
    Dim cell As Range
    Dim total As Double
    total = 0
    
    For Each cell In Range("A1:A20")
        If IsEmpty(cell) Then
            cell.Interior.Color = RGB(255, 255, 0)  ' yellow
        ElseIf cell.Value > 0 Then
            total = total + cell.Value
        End If
    Next cell
    
    MsgBox "Sum of positive values: " & total
End Sub

This shows how you can test each cell with If statements and take different actions. The IsEmpty function checks if a cell is truly blank (no value and no formula).

Common Pitfalls and How to Avoid Them

Several mistakes trip up beginners when looping through ranges:

  • Modifying the range while looping: If you delete or insert rows inside a For Each loop, the loop may skip cells or crash. Instead, collect the cells you want to delete in a Union range and delete after the loop.
  • Using Select unnecessarily: cell.Select slows down the macro and can cause errors if the worksheet isn’t active. Work directly with the cell object.
  • Forgetting to qualify the worksheet: If your code uses Range without a worksheet reference, it refers to the active sheet. That can cause unexpected results if the user switches sheets. Use ThisWorkbook.Sheets("Sheet1").Range(...) to be explicit.
  • Infinite loop risk: With a Do While loop, ensure the condition eventually becomes false. For example, if you use Do While cell.Value <> "" and never move to the next cell, you’ll loop forever.

Performance: Looping Over Large Ranges

Looping cell-by-cell is fine for a few thousand cells, but it becomes slow on ranges with tens of thousands of cells. Each read/write to a cell has overhead. To speed things up, you can:

  • Turn off screen updating: Application.ScreenUpdating = False at the start and True at the end.
  • Read the range into an array: Load the values into a VBA array in one operation, process the array, then write back. Example:
Dim data As Variant
data = Range("A1:D10000").Value
Dim i As Long, j As Long
For i = 1 To UBound(data, 1)
    For j = 1 To UBound(data, 2)
        If IsNumeric(data(i, j)) Then
            data(i, j) = data(i, j) * 2
        End If
    Next j
Next i
Range("A1:D10000").Value = data

This processes 10,000 rows almost instantly because the array is in memory.

Alternatives to Loops: Built-in Functions and SpecialCells

Sometimes you don’t need a loop at all. Excel VBA has methods that operate on a whole range at once:

  • Range.SpecialCells – For example, Range("A1:A100").SpecialCells(xlCellTypeBlanks) returns all blank cells, which you can then format or delete in one go.
  • Range.AutoFilter – Filter and then process only visible cells with SpecialCells(xlCellTypeVisible).
  • WorksheetFunction – Use Application.WorksheetFunction.Sum or CountIf to aggregate without looping.

These are often faster and less error-prone. Use a loop when you need to make decisions per cell that can’t be expressed with a built-in function.

Frequently Asked Questions

How do I loop through all used rows in a column?

Use Cells(Rows.Count, 1).End(xlUp).Row to get the last row with data in column A, then loop from 1 to that row. For example: For i = 1 To lastRow.

Can I loop through a non-contiguous range like “A1:A5, C1:C5”?

Yes. For Each cell In Range("A1:A5, C1:C5") works; the loop visits A1:A5 first, then C1:C5. You can also use the Union method to combine multiple ranges.

Why does my loop skip cells when I delete rows?

Deleting rows inside a For Each loop shifts the remaining cells up, so the loop may skip the next cell. Instead, collect rows to delete in a Union range and delete after the loop, or loop from bottom to top using For i = lastRow To 1 Step -1.

How do I loop through cells only if they contain a formula?

Use cell.HasFormula to test. For example: If cell.HasFormula Then.

What’s the difference between Range and Cells?

Range can refer to any group of cells by address or name (e.g., Range("A1:B2")). Cells takes row and column numbers (e.g., Cells(1,1)). You can combine them: Range(Cells(1,1), Cells(2,2)).

Before running any macro that changes data, save a copy of your workbook—VBA actions cannot be undone with Ctrl+Z. Test on a small sample first.

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.