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. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “excel vba break loop”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the tool above before you touch the real workbook. 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. The point is a workflow that saves repeating the same clicks every week, but the practical win is that someone else can open the file and understand what happened without asking you.
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
Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. 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 break 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.