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. Excel for the web cannot create, run, or edit VBA macros. Open the workbook in the Excel desktop app to work with macros.
On this page8
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. Before the code does anything, check the environment it needs: desktop Excel, a macro-enabled workbook (.xlsm) and macros allowed for this file. If the workbook came by email or download, Office can block its macros until the file is trusted. 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 outcome you expected 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. Run a new macro on a copy of the workbook first. Do not count on Undo to reverse what a macro changed; the saved copy is your way back.
In Google Sheets
Google Sheets does not run VBA. A macro written for Excel has to be rewritten in Google Apps Script (Extensions ▸ Apps Script), and an .xlsm file converted to Sheets keeps its cells but drops the code. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. The short version of “excel vba break loop”: paste the code, keep the workbook macro-enabled, trust the file only if you know where it came from, and test on a copy. Code that refers to the active sheet acts on whichever sheet is active, so check that before you run it.
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.