In Excel: Use For i = 1 To n ... Next i when you know the count, For Each item In collection ... Next item to walk a collection, and Do While condition ... Loop when the end is decided by a test rather than a number.
VBA macro: VBA Loops: For, For Each and Do While
Sub LoopExamples()
Dim ws As Worksheet
Dim cell As Range
Dim i As Long
Set ws = ActiveSheet
' 1. For ... Next — a counted loop
For i = 1 To 5
Debug.Print "For pass " & i
Next i
' 2. For Each — walk a collection without an index
For Each cell In ws.Range("A1:A5")
Debug.Print "For Each " & cell.Address & " = " & cell.Value
Next cell
' 3. Do While — repeat while a condition holds
i = 1
Do While i <= 5
Debug.Print "Do While pass " & i
i = i + 1
Loop
End SubPress Ctrl + G in the editor to open the Immediate window before running — that is where Debug.Print writes. All three loops produce the same five passes, which makes the structural differences easy to compare side by side.
How to run this macro
- Press Alt + F11 to open the VBA editor.
- Insert > Module.
- Paste the code above.
- Press
F5, or close the editor and run it from Developer > Macros. - Save the file as .xlsm so the macro is kept.
Decide what controls the end of the loop: a known count, a collection, or a condition.
For a count, write For i = 1 To n and close it with Next i — add Step -1 to iterate backwards.
For a collection, declare a variable of the member type (Dim cell As Range) and write For Each cell In someRange ... Next cell.
For a condition, use Do While test ... Loop and make sure something inside the body can eventually make the test false.
Leave early with Exit For or Exit Do; press Ctrl + G first so Debug.Print output is visible while you test.
What this does
VBA has three loop shapes and picking the right one removes most loop bugs before they happen. For ... Next is the counted loop: you name a variable, a start and an end, and VBA increments for you. Add Step -1 to count backwards, which is mandatory when deleting rows — going forwards, each deletion shifts the rows below up and the loop silently skips one. For Each ... Next asks a collection to hand over its members one at a time, so it needs no index and cannot run off the end; it is the natural choice for every worksheet in a workbook, every cell in a range, or every file in a folder. Do While ... Loop tests a condition before each pass and Do Until inverts the test; both suit work whose length is not known in advance, such as reading down a column until a blank appears. Exit For and Exit Do leave early — VBA has no Break or Continue keyword. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. Treat “excel vba for each loop” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That turns a workflow that saves repeating the same clicks every week into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
Paste Sub LoopExamples() into a module, press Ctrl + G to show the Immediate window, and run it with F5. The output shows five "For pass" lines, then five "For Each" lines naming the cell addresses in A1:A5, then five "Do While" lines — the same five passes reached three different ways. Where the difference bites is in real work. To delete every row whose column A is blank, count backwards with For i = lastRow To 1 Step -1, because a forward loop skips a row every time it deletes one. To touch every sheet in the file, For Each ws In ThisWorkbook.Worksheets is shorter and safer than indexing by number. To find the first empty row without knowing where the data ends, Do While Cells(r, 1).Value <> "" with r = r + 1 inside stops exactly where the data does. Loops are what separate a macro from a recorded click sequence — the recorder cannot produce one. Any job phrased as "for every row", "for each sheet" or "keep going until" is a loop, and choosing the shape that matches the phrasing is what keeps the code short enough to still be readable in six months. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.
In Google Sheets
Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. Treat “excel vba for each loop” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.
Common mistakes
- Deleting rows in a forward loop. Every deletion pulls the rows below up by one while the counter still advances, so exactly half the matching rows are skipped. Iterate with Step -1 from the last row instead.
- Writing a Do While whose condition never turns false — the body must change something the test reads, or Excel hangs. Press Esc or Ctrl + Break to regain control.
- Looping over cells one at a time on large ranges. Reading the range into a Variant array, looping over the array, and writing the result back once is often a hundred times faster than touching the sheet each pass.
- Reaching for Break or Continue out of habit. VBA spells the first Exit For / Exit Do, and has no direct equivalent of the second — wrap the rest of the body in an If instead.
- Declaring the For Each variable with the wrong type. For Each cell In Range(...) needs Dim cell As Range; a Variant works but silently gives up compile-time checking.
Frequently asked questions
What is the difference between For and For Each?
For counts through numbers you supply, so you control the order and can step backwards. For Each asks the collection for its members in its own order, needs no index, and cannot overrun — but it cannot iterate backwards.
How do I break out of a loop early?
Exit For inside a For or For Each, Exit Do inside a Do loop. Both jump straight to the line after the loop. Exit Sub leaves the whole procedure instead.
When should I use Do While rather than For?
When the number of passes is not known before the loop starts — reading down a column until a blank, or retrying until an operation succeeds. If you can compute the count in advance, For is clearer.
My loop is very slow. What helps most?
Setting Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual for the duration, and reading the range into an array instead of reading each cell. Restore both settings before the Sub ends.
Other ways people ask this
On the way here you may have searched this as “for each loop excel vba” and “for each loop in vba excel” — it is all the same task, and this page is the single, complete answer to it.
Why do people search for this in so many different ways?
Because the same task has many names. “for each loop excel vba”, “for each loop in vba excel” all point at the one operation explained on this page, which is why they all lead here.