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. For “excel vba if loop”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. 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 is what makes a workflow that saves repeating the same clicks every week useful in real work: repeatable, auditable, and not dependent on memory or luck.
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. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.
In Google Sheets
If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. 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. If you take one thing from this page on “excel vba if loop”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.
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.