VBA Loop Structures: How to Choose and Use Each Type

VBA has three main loop structures: For...Next for a known number of repetitions, For Each...Next for processing items in a collection, and Do...Loop for repeating until a condition changes. Choose the structure that makes the stopping rule clear, then make sure every loop can finish safely.

What VBA loops do

A loop repeats a block of Visual Basic for Applications (VBA) statements. In Excel, that can mean checking a range of cells, working through worksheets, or repeating a calculation until a value meets a condition. Without a loop, similar work often requires writing the same instructions many times; with a loop, the instructions are written once and applied repeatedly.

Every loop needs a control rule. A counter can track how many times a block has run, or a condition can determine whether it should run again. A reliable loop also has a way to stop: its counter reaches the endpoint, its condition becomes false, or an explicit exit statement is reached. If none of those things can happen, the macro may run indefinitely.

VBA runs in desktop versions of Excel, including Excel for Microsoft 365 on Windows. Excel for the web can open workbooks containing macros, but it cannot run or edit VBA macros; use the desktop app for that. VBA is also available in desktop Excel for Mac, though Windows-specific components such as certain system automation interfaces may not work on Mac.

For…Next: repeat a set number of times

Use For...Next when the number of repetitions is known or can be expressed as a starting value, an ending value, and an optional increment. A counter takes the starting value, runs the body, and advances by the step until it passes the endpoint. If no step is specified, VBA uses a step of 1.

This example adds numeric values from B2 through B10 on a worksheet named Data, then writes the total to D2. It skips cells that are not numeric.

Sub SumNumericCells()
    Dim ws As Worksheet
    Dim rowNumber As Long
    Dim total As Double

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Check rows 2 through 10.
    For rowNumber = 2 To 10
        If IsNumeric(ws.Cells(rowNumber, 2).Value2) Then
            total = total + CDbl(ws.Cells(rowNumber, 2).Value2)
        End If
    Next rowNumber

    ws.Range("D2").Value = total
End Sub

In this example, the counter is a Long, a suitable choice for worksheet row numbers. VBA evaluates the start, end, and step expressions when it enters the loop; changing a variable used for the endpoint inside the loop does not make the loop recalculate that endpoint on every pass. Keep the endpoint logic straightforward so the number of iterations is easy to reason about.

A step can move the counter by more than one, or count downward. For example, For rowNumber = 10 To 2 Step -2 visits 10, 8, 6, 4, and 2. The step must not be zero. If the step moves away from the endpoint, the body does not run: a loop starting at 1 and ending at 5 with a step of -1 has no iterations.

For Each…Next: process items in a collection

Use For Each...Next when the task is to visit every item in a collection and the item itself matters more than its position. In Excel, common collections include cells in a range and worksheets in a workbook. This avoids maintaining a separate numeric index simply to fetch each item.

The following macro converts non-empty text values in A2:A10 on the Data sheet to uppercase. It uses a Range variable for each cell.

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

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Process each cell in the specified range.
    For Each cell In ws.Range("A2:A10")
        If Len(cell.Value2) > 0 Then
            cell.Value2 = UCase$(CStr(cell.Value2))
        End If
    Next cell
End Sub

For a range of cells, declare the iteration variable as a Range. When iterating over an array, the control variable generally needs to be a Variant; when iterating over objects, it needs an appropriate object type. Use a type that matches the items being visited, rather than assuming every For Each loop uses the same declaration.

A useful distinction is that For Each does not directly tell you an item’s row number or position. If your logic depends on the position, use For...Next with an index, or obtain the position separately. Also take care when changing a collection while iterating over it: removing or adding items can make the traversal unreliable or produce errors, depending on the collection. If items must be removed, a reverse index loop is often easier to control.

Do…Loop: repeat while a condition applies

Use Do...Loop when repetition depends on a condition rather than a fixed count. A condition tested before the body makes the loop a pre-test loop: it may run zero times. A condition tested after the body makes it a post-test loop: the body runs at least once.

These are the common forms:

Do While condition ... Loop continues while the condition is true.
Do Until condition ... Loop continues until the condition is true.
Do ... Loop While condition runs the body first, then repeats while the condition is true.
Do ... Loop Until condition runs the body first, then repeats until the condition is true.

For example, this pre-test loop searches down column A until it reaches an empty cell or the last worksheet row. The row limit prevents an error if every cell in the column is populated.

Sub FindFirstBlankRow()
    Dim ws As Worksheet
    Dim rowNumber As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    rowNumber = 2

    ' Stop at the first blank cell or the worksheet limit.
    Do While rowNumber <= ws.Rows.Count
        If Len(ws.Cells(rowNumber, 1).Value2) = 0 Then Exit Do
        rowNumber = rowNumber + 1
    Loop

    If rowNumber <= ws.Rows.Count Then
        ws.Range("D2").Value = rowNumber
    End If
End Sub

Because the condition is checked before the body, the loop can stop without running if its starting condition is already false. In contrast, a post-test form such as Do ... Loop While condition always runs its body once. That difference matters when the body reads a cell, prompts for input, or performs a change that should not happen unless a condition has already been checked.

How to choose the right loop

Start with the stopping rule, not with a favorite syntax. Ask whether you know the number of passes, whether you need to visit every member of a collection, or whether the task should continue until a condition changes. The answers usually point to the clearest structure:

Choose For...Next for a fixed range of counts or when a numeric index is needed.
Choose For Each...Next to process every object or item in a collection without using its position.
Choose Do...Loop when the loop should end in response to a condition, such as finding a blank cell or reaching a target.

For instance, processing every worksheet in a workbook is naturally expressed with For Each ws In ThisWorkbook.Worksheets. Processing rows 2 through 100 by row number is naturally expressed with For rowNumber = 2 To 100. Searching until a user-entered value is valid calls for a condition-based loop, but the code should also provide a clear way for the user to cancel or correct the input.

Clarity matters more than making a loop as short as possible. A reader should be able to identify what is repeated, how progress is made, and what stops repetition. If those details are difficult to explain, simplify the condition or split the task into smaller procedures.

Control flow, nested loops, and safe exits

Exit For leaves the innermost active For loop, and Exit Do leaves the innermost active Do loop. They are useful when a search finds its answer early or an error condition makes further iterations unnecessary. Use them for a meaningful reason and keep the normal stopping rule visible; a loop with many scattered exits can be harder to follow.

Nested loops place one loop inside another. They are useful for comparing rows and columns, but their work multiplies. If an outer loop runs 100 times and an inner loop runs 200 times for each outer pass, the inner body executes 20,000 times. For worksheet tasks, that scale can matter, particularly when each pass reads from or writes to the worksheet.

For better performance on large ranges, consider reading a block of cells into a VBA array, processing the values in memory, and writing results back in a block. Avoid adding optimization settings without a plan to restore them if an error occurs. If a macro temporarily changes application settings, such as screen updating, preserve the original values and restore them during both normal completion and error handling.

Long-running loops may also make Excel appear unresponsive. DoEvents can allow pending events and interface updates to be processed, but it can also let other code run before the current procedure finishes. That can create re-entry or state problems. It is not a substitute for a sound stopping condition, and it should be added only when its effects are understood.

Common loop mistakes and how to prevent them

No progress toward the stopping condition: A Do While loop that never changes the value being tested can run forever. Update the relevant value in the body, or provide a deliberate exit.
Wrong endpoint or step direction: Check the starting value, ending value, and step together. A downward loop needs a negative step, and the start must be on the correct side of the endpoint.
Off-by-one errors: Decide whether the first and last values should be included. VBA’s For...Next includes the endpoint when the counter lands on it.
Unexpected blank-cell behavior: Decide whether a truly empty cell and a formula returning an empty string should both count as blank. Len(cell.Value2) = 0 treats either as having zero-length content.
Changing the wrong object: When a For Each loop edits worksheet cells, confirm the range and workbook are the intended ones. Qualifying references with a worksheet variable is safer than relying on whichever sheet happens to be active.

Test a macro on a small, representative range before applying it to important data. VBA changes are not undone by pressing Ctrl+Z, so save a separate copy of the workbook before running a macro that edits cells, deletes content, or changes workbook structure. This precaution is useful even when the loop looks simple.

Frequently Asked Questions

What are the main types of loops in VBA?
The main choices are For...Next, For Each...Next, and Do...Loop. While...Wend is also part of VBA, but it is a legacy form; Do While...Loop is generally more flexible because it supports Exit Do.

What is the difference between For and For Each in VBA?
For...Next uses a counter and is useful when the number or position of iterations matters. For Each...Next visits each item in a collection, such as each cell in a range, without requiring you to manage an index.

Does a Do While loop run at least once?
Not if its condition is tested at the top: Do While condition ... Loop can run zero times. A post-test form, Do ... Loop While condition, runs the body once before testing whether to repeat.

How do I stop a VBA loop early?
Use Exit For inside a For loop or Exit Do inside a Do loop. These statements leave the innermost loop of the matching type, so nested loops may need additional logic if more than one level must stop.

Why is my VBA loop running forever?
Usually, the loop’s condition never becomes false because the value it depends on is not updated, or the exit condition cannot be reached. Check the starting state, the change made on every pass, and any explicit exit statements; test with a small input before running on a full workbook.

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.