VBA Loop Statements: Types, Examples, and When to Use Them

A VBA loop statement repeats a block of code, either for a set number of passes or while a condition is true or false. In Excel VBA, the main choices are For...Next, For Each...Next, and Do...Loop; choose the one that best matches what determines when the repetition should stop.

What a VBA loop does

A loop lets a macro perform the same kind of operation repeatedly without copying the same instructions into the procedure many times. For example, a macro can check each row in a worksheet, visit every cell in a range, or keep asking for input until it meets a rule. The statements inside the loop are called its body.

Every loop needs a way to make progress toward ending. A counted loop advances a counter; a conditional loop changes or checks a condition. If the stopping condition never becomes true, a macro can run indefinitely or appear frozen. That is why the choice of loop and the code that changes its counter or condition both matter.

VBA tests conditions using Boolean expressions, such as rowNum <= lastRow. The comparison operators include less than (<), greater than (>), equal to (=), and not equal to (<>). A condition can also combine tests with And or Or.

Use For…Next for a known number of repetitions

For...Next is usually the clearest option when the number of passes is known or can be calculated before the loop begins. Its counter starts at a value, advances by a step amount, and the loop ends when the counter passes the limit. This example numbers rows 2 through 6 on a worksheet named Data:

Sub NumberRows()
    Dim ws As Worksheet
    Dim rowNum As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    For rowNum = 2 To 6
        ws.Cells(rowNum, 3).Value = rowNum - 1
    Next rowNum
End Sub

The macro puts 1 through 5 in cells C2:C6. The optional counter name after Next makes the loop easier to read, especially when loops are nested. Use Long for row numbers and other whole-number counters in modern Excel; worksheet row counts can exceed the capacity of VBA’s older Integer type.

A Step clause changes how the counter advances. For example, For i = 10 To 2 Step -2 visits 10, 8, 6, 4, and 2. A positive step is the default. The start, end, and step should make sense together: a counter that starts above the limit with a positive step will not run through the range as intended.

Use For Each to visit items in a range or collection

For Each...Next is designed for processing each item in a collection or array. It is often a natural fit for worksheet cells because the code does not need to manage a row counter just to visit every cell. This example highlights cells in A2:A6 that contain no value:

Sub MarkBlankCells()
    Dim ws As Worksheet
    Dim cell As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    For Each cell In ws.Range("A2:A6").Cells
        If Len(cell.Value) = 0 Then
            cell.Interior.Color = vbYellow
        End If
    Next cell
End Sub

The loop variable is declared as a Range because each item in this range is a cell. For a collection of objects, use a compatible object variable; for an array, a Variant is commonly used as the iteration variable. A useful distinction is that For Each visits the items supplied by the collection, while For...Next gives direct control over a numeric index or counter.

Be cautious about changing a collection while iterating over it. Removing or adding items can affect which items remain to be visited, depending on the collection. For worksheet work, it is generally clearer to loop through a stable range or to collect items for later changes.

Use Do…Loop when a condition controls the end

A Do...Loop is useful when the number of repetitions is not known in advance. You can test a condition at the start or at the end of the loop. A pre-test loop checks before running its body, so it may run zero times:

Sub CheckRows()
    Dim ws As Worksheet
    Dim rowNum As Long
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    rowNum = 2

    Do While rowNum <= lastRow
        If Len(ws.Cells(rowNum, "A").Value) = 0 Then
            ws.Cells(rowNum, "B").Value = "Missing"
        End If
        rowNum = rowNum + 1
    Loop
End Sub

Here, Do While continues as long as the row number is within the last used row in column A. The line that increments rowNum is essential. Without it, the condition would remain true on the same row and the macro could get stuck. In this example, if column A has no data below its header, the calculated last row may be 1 and the body will not run.

You can write the equivalent stopping logic with Do Until, which repeats until a condition becomes true. A post-test form, such as Do followed by the body and then Loop While condition, checks only after the body. It therefore runs at least once. Choose pre-test or post-test based on whether the first pass should happen when the condition is initially false.

How to choose the right loop

Pick the loop based on what defines one repetition and what defines completion. A quick guide:

Use For...Next for a known range of numbers, rows, columns, or attempts.
Use For Each...Next when each item in a range, array, or collection should be visited.
Use Do While or Do Until when a condition determines how long the operation continues.
Use a nested loop when each item in one group must be paired with items in another group, and keep track of both counters.

For example, if a macro must inspect rows 2 to 500, a counter-based loop is straightforward. If it must inspect every cell in a selected range without relying on row numbers, For Each is expressive. If it reads records until it finds a blank marker or reaches the end of a changing list, a Do loop can fit—but add a reliable exit condition and, where appropriate, a separate upper bound.

Stopping early and avoiding infinite loops

Exit For leaves the innermost active For loop, and Exit Do leaves the innermost active Do loop. These statements can be useful when a search finds its target or when an exceptional condition makes further passes unnecessary. For example, a loop searching a range can stop as soon as it finds a matching value rather than checking every remaining cell.

Use early exits deliberately. A loop with several exit points can be harder to understand than one with a clear condition at its start. With nested loops, an Exit For exits only the inner loop, not every enclosing loop. If the outer loop must also stop, make that intent explicit with a flag or another clearly structured condition.

Before running a loop, check that its counter or condition changes in the right direction and that the boundary is correct. Off-by-one errors are common: For i = 2 To 6 includes both 2 and 6, so it runs five times. For Do loops, check whether the condition is tested before or after the body. Save a copy of the workbook before running a macro; VBA changes cannot be undone with Ctrl+Z.

Excel performance and worksheet references

Loops are not automatically slow, but repeatedly reading or writing worksheet cells can take longer than doing the same work in memory. For a small range, a cell-by-cell loop is often simple and adequate. For thousands of cells, consider reading a range into a VBA array, processing the array, and writing the results back in one operation. That reduces repeated communication between VBA and the worksheet.

Always qualify worksheet references when the macro should work on a particular sheet. In the examples, ws.Cells and ws.Range refer to the worksheet assigned to ws. Unqualified references such as Cells can refer to the active sheet, which may not be the one intended if the user switches sheets. Also confirm that a loop’s range is bounded: accidentally iterating over an entire worksheet can create a very long run.

Common loop mistakes and older syntax

A frequent mistake is using a condition that can never change. For instance, a Do While loop that checks whether rowNum <= lastRow must update rowNum or otherwise change the condition inside the body. Another is using the wrong comparison: < excludes the limit, while <= includes it. Test boundary cases, such as a range with one item or no data rows.

VBA also supports the older While...Wend form. It is a pre-test loop, but unlike Do...Loop, it has no matching Exit While statement. For new code, Do While...Loop is generally more flexible and is easier to exit early. Avoid mixing loop styles merely for variety; a consistent, readable structure makes maintenance safer.

Finally, declare your variables and use meaningful names such as rowNum, lastRow, and cell. Option Explicit at the top of a module requires variables to be declared and helps catch spelling mistakes that could otherwise create unintended variables.

Frequently Asked Questions

What is the basic VBA loop statement?
The basic counted form is For counter = start To end, followed by the statements to repeat and Next counter. VBA also provides For Each...Next and Do...Loop for other kinds of repetition.

What is the difference between For and For Each in VBA?
For...Next advances a numeric counter, which is useful when the index matters. For Each...Next visits each item in a collection or array, which is convenient when you need the items themselves rather than their numeric positions.

How do I make a VBA loop run until a condition is met?
Use Do Until condition to test before each pass, or put Loop Until condition after the body to test afterward. Make sure the loop body can change the condition, or that another event can do so, to prevent an endless loop.

How do I stop a VBA loop early?
Use Exit For inside a For loop or Exit Do inside a Do loop. In nested loops, either statement exits only the innermost loop of its type.

Can I use a loop to go through every row in Excel?
Yes. A For...Next loop works well when the row bounds are known, while For Each can visit cells in a range. Calculate or define the range carefully, qualify it with the intended worksheet, and avoid scanning more cells than the task requires.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.