🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA For Each in Excel — Loop a Collection Without Touching an Index (and the Traps It Hides)

|

VBA For Each in Excel — Loop a Collection Without Touching an Index (and the Traps It Hides)

TL;DR — For Each item In collection walks every member of a collection and hands you one at a time — no counter, no .Count, no off-by-one. Use it when you have a collection (a Range, the Worksheets, an array) and you want to touch every item. Reach back for For i = 1 To N only when you need the index itself: to go backwards, to skip with Step, or to delete items as you go. Three rules keep it from betraying you: the loop variable must be an object or a Variant, iterating an array is read-only, and you must never remove items from the collection you're iterating.

Dim cell As Range
Dim total As Double

For Each cell In Sheets("Data").Range("B2:B11")
    total = total + cell.Value      ' VBA hands you each cell in turn
Next cell

Debug.Print total                   ' no i, no .Count, no bounds to get wrong

Most loops in real macros are really saying the same thing: "for every cell in this range…", "for every sheet in this workbook…", "for every name in this list…". For Each is VBA letting you write that sentence almost literally. The counter-based For i = 1 To N can express the same idea, but it makes you manage the index — start value, end value, the .Count, the fencepost. For Each takes that bookkeeping away. The catch is that it takes away the index entirely, and a few operations genuinely need it.

What you'll learn

  • The mental model — For Each iterates membership, not positions
  • Why the loop variable has to be a Range, a Worksheet, or a Variant — never a Long
  • The read-only trap: assigning to the loop variable of an array does nothing
  • Why deleting inside a For Each silently corrupts the walk — and what to do instead
  • A clear rule for when to pick For Each and when to fall back to For…Next

The mental model: For Each iterates membership, not positions

A counter loop thinks in positions: "go to slot 1, then slot 2, then slot 3." For Each thinks in membership: "give me each thing that belongs to this group, in whatever order the group keeps them." You never say where an item is; you just receive it.

That shift is why For Each is shorter and, on large ranges, slightly faster. With Cells(i, 1) VBA has to compute the address and resolve the cell on every iteration. With For Each cell In rng the collection's built-in enumerator hands you an already-resolved Range object each time — no arithmetic. You trade away the index number for a cleaner, faster walk. Everything that follows is a consequence of not having that index.

The rule that decides the loop variable's type: object or Variant, never Long

This is the first thing that trips people up, because it's the opposite of a counter loop. In For i = 1 To 10, i is a Long. In a For Each, the loop variable receives a whole item from the collection, so it must be able to hold that item:

' Range of cells -> the item is a Range
Dim cell As Range
For Each cell In Range("A1:A10")
    cell.Value = cell.Value * 2
Next cell

' Worksheets -> the item is a Worksheet
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    Debug.Print ws.Name
Next ws

Declare Dim cell As Long for a range walk and VBA throws "Object required" (error 424) the moment it tries to stuff a Range into a number. The rule: the loop variable's type must match the kind of thing the collection holds — Range for cells, Worksheet for sheets, Workbook for open books.

There's one exception, and it's the one people forget: arrays. A VBA array can hold anything, so the compiler can't know the element type up front. Iterating an array with For Each requires a Variant:

Dim names As Variant
names = Array("Alice", "Bob", "Carol")

Dim n As Variant          ' MUST be Variant for an array walk
For Each n In names
    Debug.Print n
Next n

Use Dim n As String there and you'll get a type-mismatch error. When in doubt on an array, the loop variable is a Variant.

The rule that catches everyone once: iterating an array is read-only

Here is a bug that looks like it should work and doesn't:

Dim nums As Variant
nums = Array(1, 2, 3)

Dim x As Variant
For Each x In nums
    x = x * 10            ' looks like it multiplies each element...
Next x

Debug.Print nums(0), nums(1), nums(2)   ' -> 1  2  3   (UNCHANGED)

You multiplied x, not the array. When For Each walks an array, the loop variable is a copy of the element, not a live handle to it. Assigning to x changes the copy and throws it away on the next iteration. The array never moves.

This is the single most important thing to know about For Each, because it fails silently — no error, just stale data. The fix is to drop back to a counter loop, which addresses the real element by index:

Dim i As Long
For i = LBound(nums) To UBound(nums)
    nums(i) = nums(i) * 10     ' THIS actually changes the array
Next i

Note the asymmetry: iterating a Range with For Each is writable (cell.Value = … reaches through the Range object to the real cell), because cell is an object reference, not a copy. It's specifically arrays — and value-type elements — that are read-only under For Each. If your loop needs to rewrite array contents, use For i = LBound To UBound.

The rule you learn the hard way: never delete from the collection you're walking

For Each leans on the collection's enumerator, and that enumerator assumes the collection doesn't change shape underneath it. Remove an item mid-walk and the enumerator loses its place — it skips items, or raises an error, and it never tells you which.

' BROKEN — deleting shifts the collection under the enumerator
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
    If sh.Name Like "Temp*" Then sh.Delete   ' skips sheets, unpredictable
Next sh

The same trap bites when you delete worksheet rows, remove items from a Collection, or drop keys from a Dictionary inside a For Each. The rule is absolute: if the loop changes the collection's membership, don't use For Each. Walk it with a counter backwards instead, so removing an item never disturbs the indices you haven't visited yet:

' CORRECT — count down so deletions don't shift what's left
Dim i As Long
For i = ThisWorkbook.Worksheets.Count To 1 Step -1
    With ThisWorkbook.Worksheets(i)
        If .Name Like "Temp*" Then .Delete
    End With
Next i

This is the same reverse-iteration discipline you use to delete spreadsheet rows safely — see the Step -1 pattern in VBA For Loop. The guiding idea: For Each is for reading and mutating items in place; a descending counter loop is for adding or removing them.

When to use For Each vs For…Next

Neither loop is "better" — they answer different questions. Pick by what the task needs from the index:

  • Use For Each when you have a collection and want to touch every member in its natural order: sum a range, format every cell, print each sheet name, process each item once. Cleaner to read, no bounds to get wrong, and faster on large ranges.
  • Use For i = 1 To N when you need the index as a value — to write to row i, to walk backwards (Step -1), to skip (Step 2), to compare item i with item i+1, or to delete as you go. It's also the only writable way to rewrite an array's elements.

A useful tell: if you find yourself calling .Count and manufacturing your own i just to reach collection(i), you probably wanted For Each. If you're reaching for the item before or after the current one, or removing items, you need the counter.

How ExcelMaster helps

Choosing the loop, typing the loop variable correctly, remembering that arrays are read-only and that deletions must go backwards — this is exactly the kind of mechanical judgement that stands between "I know what I want" and "the macro does it right."

ExcelMaster lets you state the goal instead. Say "in the Orders sheet, highlight every row where the status is Overdue" or "delete every sheet whose name starts with Temp," and it writes and runs the logic — picking For Each where a plain walk is right and a descending counter where items get removed, so the deletion-order bug never happens. When you do hand-write a scheduled macro, you'll still choose the loop yourself. For the everyday "just do this to today's data" task, describing the result beats getting every enumerator rule right by hand.

Frequently asked questions

What is the difference between For Each and For…Next in VBA?

For Each iterates a collection by membership — it hands you each item in the collection's natural order and you never manage an index. For i = 1 To N is counter-based — you control the index, so you can go backwards with Step -1, skip with Step 2, or reach item i+1. Use For Each for a straight walk over a Range, Worksheets, or array; use For…Next when you need the index, want to delete items, or must rewrite array elements.

Why do I get "Object required" (error 424) in a For Each loop?

Almost always because the loop variable is declared as the wrong type — usually a Long or String when the collection holds objects. For Each cell In Range(...) needs Dim cell As Range, and For Each ws In Worksheets needs Dim ws As Worksheet. If you're iterating a VBA array, the loop variable must be a Variant.

Can I change array values inside a For Each loop?

No. When For Each walks an array, the loop variable is a copy of each element, so assigning to it changes the copy, not the array — with no error. To modify the array, use a counter loop: For i = LBound(arr) To UBound(arr) and assign to arr(i). Iterating a Range with For Each is writable, because the loop variable is a live object reference to the cell.

How do I loop through every cell in a range with For Each?

Declare the loop variable as Range and iterate the range directly: For Each cell In Range("A1:B10"). VBA walks the cells row by row. For a large block this is faster than nested Cells(i, j) loops, but the fastest approach of all is to read the range into an array in one shot (arr = Range("A1:B10").Value) and loop the array — see VBA Array.

Why does deleting rows or sheets inside For Each skip some of them?

Because For Each relies on an enumerator that assumes the collection's membership doesn't change while you walk it. Deleting an item shifts everything after it, and the enumerator loses its place — silently skipping items. Never delete inside a For Each. Use a counter loop that counts down (For i = .Count To 1 Step -1) so removals don't disturb the items you haven't reached yet.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-01.

Related guides: VBA For Loop · VBA Collection · VBA With · VBA Array · VBA Dictionary