A VBA While loop repeats a block of code as long as a condition is true. In Excel, you can write it as While condition ... Wend or, more flexibly, as Do While condition ... Loop; the examples below show how to choose and use each form safely.
What a VBA While loop does
A loop is useful when VBA needs to repeat an action, such as checking successive rows, counting down, or processing items until a stopping condition is met. A While loop checks its condition before each pass through the body. If the condition is false at the start, the body runs zero times. If it remains true, the body runs again.
For example, a loop that continues while a counter is greater than zero must change that counter inside the loop. Otherwise, the condition never becomes false and the procedure can keep running indefinitely. A well-designed loop therefore has both a clear condition and a reliable way to reach the stopping point.
VBA supports two common styles: the older While...Wend statement and the more versatile Do While...Loop statement. Both can repeat code based on a true-or-false test, but they do not offer the same control options.
While…Wend syntax
The basic form begins with While and ends with Wend. Put the condition after While, then place the statements to repeat between the two keywords. VBA evaluates the condition before every pass. When it evaluates to False, execution continues after Wend.
This complete procedure counts down from five in the Immediate window:
Option Explicit
Sub CountDownWithWend()
Dim counter As Long
counter = 5
While counter > 0
' Print the current value, then move toward the stopping point.
Debug.Print counter
counter = counter - 1
Wend
Debug.Print "Finished"
End Sub
To see the output, open the Visual Basic Editor and its Immediate window. The procedure prints 5, 4, 3, 2, and 1, followed by “Finished.” The counter reaches zero, so the next condition test is false and the loop ends. A Long is a sensible choice for many Excel row counters because it can hold far more than VBA’s 16-bit Integer.
Do While…Loop and Do…Loop While
Do While condition ... Loop also tests before entering the body, so it can run zero times. For example, a condition that is false initially means VBA skips the statements between Do and Loop. The other placement, Do ... Loop While condition, tests at the bottom. That form always runs the body at least once, then repeats if the condition is true.
Choose the top-tested form when the body should run only if the condition already holds. Choose the bottom-tested form when one initial pass is required, such as asking for input once before checking whether it is acceptable. Do not confuse Do While with Do Until: While repeats while the condition is true, whereas Until repeats while the condition is false.
For most new VBA code, Do While...Loop is a practical default. It supports Exit Do, which lets a procedure stop early when another condition is met. While...Wend does not provide a corresponding Exit While statement, so it is less convenient when a loop needs more than one possible exit.
Example: read rows until a blank cell
A common Excel task is to inspect entries in column A, starting at row 2, and stop at the first blank. This example prints each entry to the Immediate window. It assumes the list is contiguous: a blank cell marks the end, and there are no meaningful entries below that blank.
Option Explicit
Sub ReadUntilBlank()
Dim ws As Worksheet
Dim rowNum As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
rowNum = 2
Do While Len(ws.Cells(rowNum, 1).Value) > 0
' Inspect the current entry, then advance one row.
Debug.Print ws.Cells(rowNum, 1).Value
rowNum = rowNum + 1
Loop
End Sub
Change "Sheet1" to the actual worksheet name. The condition uses Len to treat an empty cell or a formula returning an empty string as having no text. If a cell contains an error value, however, this expression can raise an error; validate or handle error values separately if they are possible in the data.
This pattern stops at the first gap. If column A can contain blanks between records, use a known last row as the limit instead. That way, a blank in the middle does not accidentally hide later records.
Example: process a bounded range
When a list can contain gaps, find the last used row in the relevant column and loop up to that row. The following version prints nonblank values in column A from row 2 onward. It uses the last nonempty cell found by Excel’s upward search from the bottom of the sheet.
Option Explicit
Sub ReadThroughLastRow()
Dim ws As Worksheet
Dim rowNum As Long
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
rowNum = 2
Do While rowNum <= lastRow
' Skip empty cells but keep checking later rows.
If Len(ws.Cells(rowNum, 1).Value) > 0 Then
Debug.Print ws.Cells(rowNum, 1).Value
End If
rowNum = rowNum + 1
Loop
End Sub
If column A contains only a header, lastRow will be 1 and the loop will not run. This approach defines the end of the data by column A, so choose a column that is reliably populated when identifying records. A formula that displays an empty string may still count as a used cell for Excel’s navigation, which can make the loop extend farther than the visible data.
How to choose the right loop
A While loop is not always the best loop for a task. Match the structure to what is known about the work:
- Use
For...Nextwhen you know the number of repetitions or are stepping through a fixed range of row numbers. Its counter update is built into the loop structure. - Use
For Each...Nextwhen you want to visit each object in a collection, such as every worksheet in a workbook or each cell in a range. - Use
Do While...Loopwhen repetition depends on a condition that may change as the procedure runs, such as “continue until this cell is blank.” - Use
Do Until...Loopwhen describing the stop condition as a condition that must eventually become true makes the logic clearer.
These choices are about clarity, not a rule that one loop is always faster. For a simple fixed row range, For...Next often makes the bounds obvious. For a search or a process whose length is not known in advance, a Do loop can express the stopping rule more naturally.
Prevent infinite loops and other errors
The most common loop problem is a condition that never changes from true. In the countdown example, omitting counter = counter - 1 would leave the counter at five forever. Similarly, a row-scanning loop that forgets to increment its row number would keep checking the same cell. Review every loop and identify the statement that changes the condition or advances the position.
Other useful safeguards include:
- Set a realistic upper limit when the data has a known maximum size.
- Use
Longfor row numbers and counters that could exceed the range of anInteger. - Make the start point, end point, and stopping rule explicit, especially when the first row contains headers.
- Consider what happens with empty cells, error values, missing worksheets, or unexpected input.
- Test on a small copy of the data before running a loop across a large range.
Excel can appear unresponsive while a macro repeatedly performs work, particularly if the loop processes many cells or triggers recalculation. Keep the loop focused, avoid unnecessary worksheet reads and writes, and include a valid exit path. A long-running macro may be interrupted with Esc or Ctrl+Break on Windows, but interruption is not a replacement for a correct stopping condition.
Run VBA in Excel safely
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. On Mac, the editor shortcut is generally Option+F11; some keyboards require Fn as well. Insert a standard module, paste the procedure, and run it from the editor using Run > Run Sub/UserForm or the editor’s Run button. The Immediate window is available in the editor, commonly through Ctrl+G on Windows; on Mac, use the View menu if the shortcut differs on your keyboard.
Save a copy of the workbook before running a macro that changes data. Excel macros cannot be undone with Ctrl+Z, even if the macro makes an unintended edit. The examples here only print values, but a loop that writes to cells can change many records quickly. Use a macro-enabled workbook format such as .xlsm if you need to keep VBA code. Excel for the web can open macro-enabled workbooks but cannot create, run, or edit VBA macros; use the desktop app for these procedures. Some Excel features and objects also behave differently on Mac, so platform-specific automation should be checked separately.
Frequently Asked Questions
What is the syntax for a While loop in VBA?
Use While condition, put the repeated statements below it, and close the block with Wend. Alternatively, use Do While condition and close with Loop. Both forms test the condition before each pass.
What is the difference between While…Wend and Do While?
Both repeat while a condition is true, but Do While...Loop gives you more control, including Exit Do and a bottom-tested form. While...Wend is a more limited, older structure and has no Exit While.
Does a VBA While loop run at least once?
Not if it tests the condition at the top. While...Wend and Do While...Loop can run zero times when the initial condition is false. Do...Loop While checks at the bottom and therefore runs its body at least once.
How do I stop a Do While loop early?
Use Exit Do inside the loop when a separate condition calls for an early exit. For example, a search can stop when it finds a matching value rather than continuing through every remaining row. Ensure the normal condition can also end the loop when no match is found.
Why is my VBA While loop not stopping?
Usually, the condition remains true because a counter or position variable is not being updated, or because the stopping condition does not match the data. Check the values used in the condition on each pass, confirm the loop advances, and add a sensible bound when appropriate.
Related
- table() in RStudio: How It Counts and Displays Data
- Data Validation and Reconciliation in Excel Explained
- PowerPoint Table Diagonal Split Cell: Practical Methods
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Error Handler: How On Error Works and When to Use ItIn VBA, an error handler is code that responds when a statement fails while a procedure is running.…
- Gamepad Macro Recorder: How It Works and What to KnowA gamepad macro recorder captures a sequence of controller inputs—such as button presses, stick movements, and pauses—and plays…
- VBA Length of String: How Len WorksIn VBA, use the Len function to get the length of a string: Len(text) returns a Long count…
- MS Access VBA String Functions: A Practical GuideMS Access VBA string functions let you extract, search, compare, clean, and assemble text in procedures and functions.…
- Excel VBA Copy an Entire Column: Safe Code and TipsYes. In Excel for Microsoft 365 on Windows, VBA can copy a complete column with a worksheet’s Columns…
- VBA: How to Find an Accredited VA PractitionerIf by VBA you mean the U.S. Department of Veterans Affairs’ Veterans Benefits Administration, use VA’s official accreditation…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.