VBA Loop Format: Syntax, Examples, and When to Use Each

A VBA loop repeats a block of code while counting through values, visiting items in a collection, or checking a condition. The main formats are For...Next, For Each...Next, and Do...Loop; choose one based on whether you know the number of repetitions, are iterating through objects, or need to keep going until a condition changes.

What a VBA loop does

A loop saves you from writing the same instruction repeatedly. Instead of addressing each worksheet cell or repeating a calculation by hand, you write the operation once and tell VBA how to repeat it. A loop can run a fixed number of times, move through a set of objects such as worksheets, or continue while a logical test is true.

In Excel for Microsoft 365 on Windows, loop code is normally placed in a procedure in a standard VBA module. Open the Visual Basic Editor with Alt+F11, choose Insert > Module, and place the procedure there. On a Mac, Option+F11 opens the editor on many keyboards; you may need to press Fn as well. The Developer tab can also open Visual Basic on either platform, although its availability depends on whether that tab is enabled.

Before running any macro that changes a workbook, save a separate copy. VBA changes cannot be reversed with Excel’s Ctrl+Z undo command, so a backup is the simplest protection while learning or adapting a loop.

For…Next: repeat a known number of times

Use For...Next when the start and end of the counting range are known. The basic structure is For counter = start To end [Step increment], followed by the statements to repeat and a matching Next counter. The optional Step sets how much the counter changes after each pass. If omitted, the increment is 1.

This example writes the numbers 1 through 5 into cells A1 through A5 on the active worksheet:

Sub WriteNumbers()
    Dim i As Long

    ' Write one number to column A on each pass.
    For i = 1 To 5
        ActiveSheet.Cells(i, 1).Value = i
    Next i
End Sub

On the first pass, i is 1; on the next, it is 2, and so on. After the pass where it is 5, the loop finishes. A Long variable is a sensible counter type for worksheet row numbers, which can exceed the range of the older Integer type.

A loop can count down or skip values. For example, For i = 10 To 2 Step -2 visits 10, 8, 6, 4, and 2. Ensure the step moves toward the end value: a positive step with a smaller starting value than ending value counts upward, while a negative step is normally used to count downward. An unsuitable direction can make the loop run zero times.

For Each…Next: visit every item in a collection

Use For Each...Next when you want to process every member of a collection without managing a numeric index. Excel collections include worksheets, workbooks, and cells in a range. This form is often clearer than counting from 1 to the collection’s item count, especially when working with objects.

The following example checks every worksheet in the active workbook and colors its tab when the sheet name begins with “Data”:

Sub MarkDataSheets()
    Dim ws As Worksheet

    ' Inspect each worksheet in the active workbook.
    For Each ws In ActiveWorkbook.Worksheets
        If Left$(ws.Name, 4) = "Data" Then
            ws.Tab.Color = RGB(0, 176, 80)
        End If
    Next ws
End Sub

The loop variable must be able to hold the items being visited. For worksheets, declare it as Worksheet; for a set of differently typed objects, a suitable object type or Variant may be needed. The name after Next is optional in VBA, but including it makes nested loops easier to read and helps identify which loop is ending.

Be careful about changing a collection while iterating through it. Deleting worksheets or removing items from a collection during a For Each loop can disrupt which item comes next. When deletion is required, a reverse-counting For...Next loop is often safer, because removing a later item does not shift the indexes that remain to be processed.

Do…Loop: repeat until a condition changes

Use Do...Loop when repetition depends on a condition rather than a fixed count. A condition at the beginning checks before each pass; if it is false immediately, the body does not run. A condition at the end is checked after the body, so the body runs at least once.

For example, this loop moves down column A until it reaches the first blank cell, then places a marker in column B on that row:

Sub MarkFirstBlankRow()
    Dim rowNumber As Long

    ' Start at row 1 and continue while column A is not blank.
    rowNumber = 1
    Do While Len(ActiveSheet.Cells(rowNumber, 1).Value) > 0
        rowNumber = rowNumber + 1
    Loop

    ActiveSheet.Cells(rowNumber, 2).Value = "First blank"
End Sub

The same basic logic can be expressed with Do Until condition, which continues until the condition becomes true. VBA also supports a trailing test, as in Do ... Loop While condition or Do ... Loop Until condition. Pick the version that makes the intended stopping rule easiest to understand. In the example, if A1 is blank at the start, the loop makes no passes and marks row 1.

A condition-controlled loop needs a reliable route to its stopping state. If the code inside never changes the value being tested, or never advances a counter used by the test, a Do loop may run indefinitely. Check what makes the condition true or false before running the macro, particularly if the loop edits cells or depends on user input.

Nested loops and loop control

A nested loop is a loop inside another loop. It is useful for working through a two-dimensional area, such as rows and columns. The inner loop completes all its passes for each single pass of the outer loop. This example visits cells in a two-row by three-column area and adds 1 to each numeric value:

Sub IncreaseSmallRange()
    Dim rowNumber As Long
    Dim columnNumber As Long

    ' Visit rows 1 to 2 and columns 1 to 3.
    For rowNumber = 1 To 2
        For columnNumber = 1 To 3
            If IsNumeric(Cells(rowNumber, columnNumber).Value) Then
                Cells(rowNumber, columnNumber).Value = _
                    Cells(rowNumber, columnNumber).Value + 1
            End If
        Next columnNumber
    Next rowNumber
End Sub

Because this example uses the unqualified Cells property, it refers to the active sheet. In reusable code, qualify the range with a worksheet variable, such as ws.Cells(rowNumber, columnNumber), so the macro does not silently operate on whichever sheet happens to be active. Nested loops can multiply the amount of work: 100 outer passes and 20 inner passes mean 2,000 inner executions.

Exit For leaves the innermost active For loop, and Exit Do leaves the active Do loop. They are useful when a search finds what it needs before reaching the normal end. Use them with a clear condition and avoid scattering exits throughout a procedure, because that can make the loop’s outcome harder to follow.

How to choose the right loop

Known count or numeric sequence: choose For...Next, such as processing rows 2 through 100 or counting down through indexes.
Every object or item in a collection: choose For Each...Next, such as inspecting every worksheet or every cell in a defined range.
Stop when a condition changes: choose Do While or Do Until, such as reading down a column until the first blank.
Run the body at least once: use a trailing condition in a Do...Loop, because its test occurs after the body.

There is not one universally fastest or best loop format for every macro. Readability and the stopping rule are usually the most important choices. If a loop is processing a large range, repeated interaction with worksheet cells can be slow; in performance-sensitive code, it may be better to read a range into a VBA array, process the values in memory, and write the results back in a batch. That approach adds complexity, so it is not necessary for a small task unless performance is actually a problem.

Common errors and safer habits

Many loop bugs come from boundaries and assumptions rather than complicated syntax. A loop from 1 to 10 includes both 1 and 10. If a worksheet operation should begin at row 2 because row 1 contains headers, set the starting row accordingly. For a reverse loop that deletes rows, start at the last row and count down so that deleting one row does not move an unprocessed row into an already-passed position.

Use these checks when writing or reviewing a loop:

Confirm the starting value, ending value, and step produce the intended sequence.
Make sure every block has its matching Next or Loop, especially when loops are nested.
For a condition-based loop, identify exactly what changes the condition and guarantees an exit.
Qualify worksheet and workbook references when the macro must work on a particular file or sheet.
Use a declared variable with an appropriate type, and give counters meaningful names when that improves clarity.
Test on a copy with a small, representative range before applying the macro to important data.

A frequent misconception is that For Each automatically means “all used cells.” It only visits the items in the collection named after In. For example, a loop over Range("A1:A10") visits those ten cells, not every nonblank cell in the worksheet. Define the range or collection deliberately, and decide how blanks, errors, and unexpected values should be handled.

Frequently Asked Questions

What is the basic VBA loop syntax?
A counting loop uses For counter = start To end, the repeated statements, and Next counter. A condition-based loop uses Do While condition or Do Until condition and ends with Loop.

What is the difference between For and For Each in VBA?
For...Next normally counts through a numeric sequence or index. For Each...Next visits each item in a collection, such as each worksheet or each cell in a specified range, without requiring you to maintain the item number.

How do I make a VBA loop run backward?
Use a negative step, such as For i = 10 To 1 Step -1, and finish with Next i. The starting value, ending value, and negative step must be aligned so the counter moves toward the endpoint.

Why does my VBA Do loop never stop?
Usually, the tested condition never changes to the stopping state. Check that the code updates the relevant value or counter on every pass and that the condition uses the intended comparison. You can stop a running macro with the editor’s Reset control, but first consider whether partially changed workbook data needs to be restored from a saved copy.

Can I use Exit For in a VBA Do loop?
No. Exit For exits a For loop; Exit Do exits a Do...Loop. Use the exit statement that matches the loop type, and remember that it exits the innermost active loop.

Can I undo a VBA loop with Ctrl+Z?
No. Excel’s normal undo history does not reverse changes made by a macro. Save a copy before running code that changes workbook contents, and test potentially destructive macros on sample data first.

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.