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. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. Treat “excel vba while 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. 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. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. If you take one thing from this page on “excel vba while 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.
Other ways people ask this
On the way here you may have searched this as “excel vba do while loop”, “while loop excel vba” and “while 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. “excel vba do while loop”, “while loop excel vba”, “while loop in vba excel” all point at the one operation explained on this page, which is why they all lead here.