VBA Loop Example: For, Do While, and For Each Loops

A VBA loop repeats a block of code, either a set number of times or until a condition is met. For example, a For...Next loop can write the numbers 1 through 5 into cells A1:A5 in Excel.

Save a copy of your workbook before running a macro: VBA changes cannot be reversed with Ctrl+Z. The following standalone macro writes to the active worksheet.

Option Explicit

Sub FillSequence()
    Dim i As Long

    ' Write 1 through 5 in cells A1:A5.
    For i = 1 To 5
        ActiveSheet.Cells(i, 1).Value = i
    Next i
End Sub

Put VBA code in a standard module in the desktop version of Excel. The examples below show the main loop patterns, when to use each, and how to avoid common mistakes.

What a VBA loop does

A loop runs one or more statements repeatedly. Each pass through the code is called an iteration. A loop needs a way to determine when to stop: a fixed number of passes, a condition that changes, or the items in a collection. The right pattern depends on what the macro is meant to process.

In Excel automation, loops commonly handle rows of records, cells in a range, worksheets in a workbook, or a sequence of calculations. A loop can read a value, make a decision, and write a result for each item. It is useful when the same operation needs to be applied consistently to many items, rather than repeated manually or copied as separate VBA statements.

The first example uses ActiveSheet, so it writes to whichever worksheet is active when the macro runs. That is convenient for a small demonstration, but a production macro may be safer if it refers to a specific worksheet by name. If the active sheet is not the intended one, the result can go somewhere unexpected.

Use For…Next for a known number of repetitions

Use For...Next when you know the starting and ending values for a counter. In the opening example, the variable i takes the values 1, 2, 3, 4, and 5. Excel writes each value into column A at the row matching the counter. The loop runs five times, and Next i advances the counter before the next pass.

A loop can count by a value other than one with the Step keyword. For example, For i = 2 To 10 Step 2 visits 2, 4, 6, 8, and 10. A negative step counts down, such as For i = 5 To 1 Step -1. The ending value is included if the count reaches it exactly; if the step skips over it, the loop ends after passing it.

Use a Long variable for row counters and many other whole-number counters. Excel worksheets have far more rows than an old VBA Integer can represent, so Long is the usual safer choice for row numbers. If the loop counter is meant to change the row being processed, check carefully whether the first row is a heading and whether the final row should be included.

Use For Each to process cells in a range

When the task is to visit every cell in a range, For Each often reads more naturally than a numeric counter. This example assumes the active worksheet has numeric amounts in A2:A20 and writes a 10% increase beside each numeric value in column B. It leaves cells with nonnumeric contents alone.

Option Explicit

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

    ' Use the active worksheet and inspect the stated range.
    Set ws = ActiveSheet
    For Each cell In ws.Range("A2:A20")
        If IsNumeric(cell.Value) Then
            cell.Offset(0, 1).Value = CDbl(cell.Value) * 1.1
        End If
    Next cell
End Sub

Here, cell represents the current cell in the range. Offset(0, 1) refers to the cell on the same row, one column to the right. For example, if A2 contains 100, B2 receives 110. The calculation is 100 × 1.1 = 110. This example is intended for a column of amounts, not a mixed column containing dates or other numeric values: Excel stores dates as numbers, so a date could also satisfy IsNumeric.

Before using this pattern on real data, confirm that the destination cells are available. The macro overwrites values already in column B. You can change the source range, but use a range that matches the data and avoid assuming that a fixed endpoint will always include every row.

Use Do While when a condition controls the end

A Do While loop continues as long as its condition is true. It is useful when the number of iterations is not known in advance, such as processing rows until the first blank cell in a key column. This example assumes names begin in A2 and that the first blank in column A marks the end of the list. It writes uppercase versions to column B.

Option Explicit

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

    ' Stop at the first blank cell in column A.
    Set ws = ActiveSheet
    rowNum = 2
    Do While ws.Cells(rowNum, 1).Value <> ""
        ws.Cells(rowNum, 2).Value = UCase$(CStr(ws.Cells(rowNum, 1).Value))
        rowNum = rowNum + 1
    Loop
End Sub

The condition is checked before each pass. If A2 is blank, the loop does not run at all. If A2:A5 contain names and A6 is blank, the macro processes rows 2 through 5 and then stops. A blank somewhere in the middle also stops the loop, even if there are more names farther down. That behavior is correct only if a blank really does mark the end of the list.

When gaps are allowed, a fixed range or a separately calculated last row is usually more suitable. The important point is that the condition must eventually become false. If the code never changes the value tested by the condition, a Do While loop can run indefinitely.

Do Until, nested loops, and other variations

Do Until is the counterpart to Do While: it keeps running until its condition becomes true. For instance, Do Until rowNum > 10 processes row numbers up to 10 if the code increments rowNum on every pass. Choose whichever form makes the stopping rule easiest to read. Avoid switching between “while” and “until” wording without checking the logic, because the conditions are opposites.

You can also place one loop inside another. A common use is processing a rectangular block of cells: the outer loop can move through rows, and the inner loop can move through columns. The inner loop completes all its passes for one outer-loop value before the outer loop advances. Nested loops can be useful for small tables, but a large number of rows and columns can mean many total iterations. For example, 100 rows times 20 columns means 2,000 cell visits.

Another variation is to loop through worksheets with For Each and a variable declared as Worksheet. That is useful for applying the same check or formatting operation across several sheets. As with range loops, make sure the code targets the intended sheets; a workbook may include hidden sheets or sheets that should not be changed.

How to choose the right loop

Choose For…Next when the number of repetitions is known, or when a numeric counter determines which row, column, or item to use.
Choose For Each when you want to visit every member of a collection, such as every cell in a range or every worksheet in a workbook.
Choose Do While or Do Until when the number of passes depends on a condition, such as reaching a blank row or meeting a target.
Choose a non-loop approach when Excel can complete the task with a direct range operation or formula. A loop is not automatically the fastest or simplest solution.

For example, a loop is a natural fit when each row needs a different decision based on its contents. If every cell in a range needs the same simple value or formula, assigning to the whole range at once may be clearer and faster. The best choice is the one that expresses the task accurately and is straightforward to maintain.

Common loop errors and how to avoid them

Off-by-one errors: Check the first and last values, and whether the endpoints are included. For a list with a header, data often begins on row 2, not row 1.
A condition that never changes: In a Do loop, update the counter or data used by the condition. Otherwise, the macro may not reach its stopping point.
Stopping at the wrong blank: A loop that stops at the first empty cell is not appropriate if blank rows can appear within the data.
Changing a collection while moving forward through it: If deleting rows in a counted loop, deleting a row shifts later rows upward and can cause one to be skipped. A common approach is to loop upward from the last row to the first, adjusting the logic to match the task.
Using the wrong worksheet: Code that depends on ActiveSheet can affect a different sheet than expected. Assign a worksheet explicitly when the target must not depend on which sheet is selected.
Using an unsuitable data type: Use Long for typical worksheet row counters. Also consider how the code should handle blanks, text, errors, and dates in the cells it reads.

For a loop that might run a long time, test it on a small sample first and make the stopping rule easy to inspect. A temporary message or a breakpoint in the Visual Basic Editor can help diagnose the counter, but remove diagnostic code before relying on the macro for regular work. Do not assume that a macro can be undone after it changes cells.

Run a VBA loop safely in Excel

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, then choose Insert > Module and paste a macro into the module. To run it, return to Excel and press Alt+F8, select the macro, and choose Run. You can also run a procedure from the Visual Basic Editor with F5 while the cursor is inside it.

On a Mac, Excel also has the Visual Basic Editor, but the keyboard shortcut can depend on the keyboard and function-key settings; Option+F11 may require holding Fn as well. Excel for the web does not create or run VBA macros. Use the desktop Excel application for these examples. If the workbook needs to retain VBA code, save it as an Excel Macro-Enabled Workbook with the .xlsm extension.

Macros can change workbook contents, and macros from an untrusted source can pose a security risk. Run only code you understand and trust, and review which sheet and cells it will affect before running it. Keep a backup, particularly when a macro overwrites values or deletes data.

Frequently Asked Questions

What is the simplest VBA loop example?

A For...Next loop is a simple starting point: set a counter from 1 to 5, write the counter to a cell, and advance it with Next. The opening example writes the numbers 1 through 5 in A1:A5.

What is the difference between For and For Each in VBA?

For...Next uses a counter and is well suited to a known sequence or a row number. For Each visits each object in a collection, such as each cell in a range, without requiring you to calculate a separate counter.

How do I loop through rows until a blank cell?

Start with the first data row, use a Do While condition that checks the chosen column for a nonblank value, and increase the row counter each pass. This stops at the first blank, so use it only when blanks mark the end of the list.

Can I use VBA loops in Excel for the web?

No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use VBA; the available keyboard shortcuts differ by operating system and keyboard.

Can I undo a VBA macro with Ctrl+Z?

Generally, no. Running VBA clears Excel’s normal undo history, so Ctrl+Z cannot reverse changes made by the macro. Save a separate copy before running code that modifies workbook data.

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.