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 Eachloop, the loop may skip cells or crash. Instead, collect the cells you want to delete in aUnionrange and delete after the loop. - Using
Selectunnecessarily:cell.Selectslows 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
Rangewithout a worksheet reference, it refers to the active sheet. That can cause unexpected results if the user switches sheets. UseThisWorkbook.Sheets("Sheet1").Range(...)to be explicit. - Infinite loop risk: With a
Do Whileloop, ensure the condition eventually becomes false. For example, if you useDo 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 = Falseat the start andTrueat 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 withSpecialCells(xlCellTypeVisible).WorksheetFunction– UseApplication.WorksheetFunction.SumorCountIfto 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
- Excel Undo Encrypt with Password? How to Remove It
- Excel Truck Group Jobs: Pay, Hiring & Reviews
- Excel Track Purdue Global: What It Is & How It Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Excel Offset: How to Select a Range ReliablyIn Excel VBA, use the Offset property on a Range to refer to cells a chosen number of…
- VBA Selection Cells: How to Use and Avoid ErrorsIn VBA, Selection.Cells refers to the collection of cells within the currently selected range, allowing you to read…
- Best TG Macro Settings for Windows 10The top pick for most Windows 10 users is the TG Macro (formerly TinyTask) with a 1–2 ms…
- Excel VBA ABS Function: Syntax and ExamplesIn Excel VBA, use the built-in Abs function to return a number’s absolute value: Abs(number). For example, Abs(-12.5)…
- Excel VBA Freeze Panes at a Specific CellYes. Activate the worksheet, select the cell that marks the top-left corner of the scrolling area, then set…
- VBA Change Worksheet Name: Code and Common IssuesTo change a worksheet name with VBA, assign a new value to the worksheet’s Name property, such as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.