Microsoft Excel VBA For Loops: Syntax and Examples

In Microsoft Excel VBA, a For...Next loop repeats instructions a set number of times, while For Each...Next repeats them for every item in a collection, such as cells in a range. Use For...Next when you need a counter or a known number of passes; use For Each when you want to work through objects directly.

How a For…Next loop works

A For...Next loop needs a counter variable, a starting value, an ending value, and a block of instructions. VBA changes the counter by one after each pass unless you specify a different step. The loop stops after the counter passes the end value. The basic structure is:

Sub CountToFive()
    Dim i As Long

    ' Repeat the instruction for values 1 through 5.
    For i = 1 To 5
        Debug.Print i
    Next i
End Sub

This example prints 1, 2, 3, 4, and 5 in the Visual Basic Editor’s Immediate window. The counter is declared as Long, a suitable choice for ordinary whole-number counters and Excel row numbers. A loop can also contain several instructions, conditional statements, or another loop.

The Next i line marks the end of the repeated block and advances the counter. Writing the counter after Next is optional in a simple loop, but it makes the matching loop easier to identify—particularly when procedures contain nested loops.

Changing the direction and step

Add Step to control how much the counter changes each time. A positive step counts upward; a negative step counts downward. If you omit it, VBA uses a step of 1.

Sub CountDownByTwos()
    Dim i As Long

    ' Print 10, 8, 6, 4, and 2.
    For i = 10 To 2 Step -2
        Debug.Print i
    Next i
End Sub

With a positive step, a starting value greater than the ending value produces no passes. For example, For i = 5 To 1 does not count down; use Step -1 to do that. Choose an end value that matches the values the counter can reach: a step of 2 from 1 to 6 visits 1, 3, and 5, not 6. This matters when a loop is meant to cover every row or every numbered item.

A counter normally changes automatically at Next. Avoid assigning a new value to the counter inside the loop unless you have a specific reason: it can make the sequence difficult to follow and may skip work or cause an unexpected loop.

Loop through worksheet rows

A common Excel task is to process each row in a worksheet. For example, with numeric quantities in column A and unit prices in column B, this macro writes each row’s product in column C. It assumes row 1 contains headings and the data begins in row 2.

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

    ' Use the Sales sheet in the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Sales")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Calculate totals for rows with data in column A.
    For rowNum = 2 To lastRow
        ws.Cells(rowNum, "C").Value = _
            ws.Cells(rowNum, "A").Value * ws.Cells(rowNum, "B").Value
    Next rowNum
End Sub

Change "Sales" to the exact worksheet name in your workbook. The End(xlUp) expression starts at the bottom of column A and moves up to its last nonempty cell, so the macro does not need a fixed final row. If column A contains no data below the heading, lastRow is 1; the loop from 2 to 1 simply makes no passes.

This approach is useful when each row needs its own calculation, formatting, or check. Make sure the chosen column reliably identifies the last data row. If column A can have gaps or stop before other columns, use a column that is consistently populated or determine the last row from the appropriate part of the sheet.

Use For Each for cells and other objects

When the task is “do this for every cell in this range,” For Each is often clearer than counting row numbers. With text values in A2:A20, this example highlights nonempty cells:

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

    ' Set the worksheet and examine each cell in the range.
    Set ws = ThisWorkbook.Worksheets("Sales")
    For Each cell In ws.Range("A2:A20")
        If Len(cell.Value) > 0 Then
            cell.Interior.Color = vbYellow
        End If
    Next cell
End Sub

The loop variable is declared as Range because each item is a cell object. You can use similar loops for worksheets in a workbook or other VBA collections, with a variable type appropriate to the objects. A range loop visits cells in the range’s normal order, which is generally left to right across each row, then down through subsequent rows.

Use a numbered For...Next loop instead when the position matters—for example, when processing every third row, using the row number in a calculation, or moving backward through rows. That explicit counter also makes it easy to refer to cells by row and column.

Choose the right kind of loop

Pick the loop based on what determines when the work is finished, not just on which syntax looks shorter:

For...Next: Choose it for a known range of counter values, such as rows 2 through the last used row, or for a fixed number of repetitions.
For Each...Next: Choose it to visit every object in a collection or every cell in a specified range without managing an index yourself.
Do While...Loop or Do Until...Loop: Choose these when repetition depends on a condition that may change during the work, rather than on a known count.

A frequent misconception is that For Each is always faster or that For...Next is always the best choice for cells. Readability and the task’s logic are usually the more useful first considerations. Both forms can be appropriate for ranges. If the macro must delete worksheet rows, a counter loop running from the bottom row toward the top is often safer than moving forward, because deleting a row shifts the later rows upward and can otherwise cause one to be skipped.

Common errors and how to prevent them

Most loop problems come from incorrect boundaries, an unsuitable counter, or a mismatch between the worksheet and the code. Check these points when a macro misses data or behaves unexpectedly:

Off-by-one ranges: Confirm whether row 1 is a heading and whether the first and last rows should be included. A loop from 2 to lastRow includes both endpoints when the bounds allow it.
Wrong sheet reference: Qualify ranges with a worksheet variable, as in ws.Cells(...). Unqualified Cells or Range can refer to the active sheet, which may not be the intended one.
Counter declared as Integer: VBA’s Integer has a much smaller range than Excel’s row capacity. Use Long for row counters so large worksheets do not exceed the variable’s limit.
Incorrect step direction: A loop intended to count down needs a negative step. A positive step with a start value above the end value does not automatically reverse direction.
Unexpected cell contents: Text, blanks, or error values may not work with an operation intended for numbers. Check or validate the data before performing arithmetic.
Endless conditional loop: In a Do While loop, make sure the instructions can eventually make the condition false. A condition that never changes can leave the macro running indefinitely.

In the Visual Basic Editor, use Debug > Compile VBAProject to catch many syntax or declaration issues. To inspect a loop, click in the procedure and press F8 to step through it one line at a time. Watching the counter and the active worksheet reference can reveal a boundary error quickly.

Performance, workbook safety, and macro limits

A loop that reads and writes one cell at a time is usually fine for modest ranges, but repeated worksheet access can become slow over hundreds of thousands of cells. For large jobs, VBA developers often read a range into an array, process the values in memory, and write results back in a block. That requires more care about array dimensions and data types, so a simple cell loop is a reasonable starting point when its speed is adequate.

Before running code that changes cell values, formatting, or rows, save a separate copy of the workbook. VBA macro actions cannot be undone with Ctrl+Z; Excel’s normal Undo history does not reverse a macro’s changes. Test on a small sample or a duplicate file first, especially before a loop that clears or deletes data.

Macros must be enabled for VBA code to run, and workbook files containing VBA should be saved in a macro-enabled format such as .xlsm. Only enable macros in files from a source you trust. Excel for the web can open and edit many workbook contents, but it cannot create, run, or edit VBA macros; use the desktop Excel application for those tasks.

Open the VBA editor and run a loop

In Excel for Microsoft 365 on Windows, open the Developer tab and select Visual Basic. If the tab is not displayed, enable it through File > Options > Customize Ribbon by selecting the Developer check box. In the editor, use Insert > Module, then place a complete procedure in the module. To run a procedure, put the cursor inside it and press F5, or return to Excel and run it through the macro tools.

The Windows shortcut Alt+F11 opens or switches to the Visual Basic Editor. On a Mac, use the Developer tab’s Visual Basic button; the keyboard shortcut may require Option+F11 or the keyboard’s Fn key, depending on its function-key settings. Excel for Mac supports VBA, but the available menus, shortcuts, and some VBA features can differ from Windows. The examples here use standard worksheet objects and are intended for desktop Excel.

Frequently Asked Questions

What is the difference between For and For Each in VBA?
For...Next advances a counter through a specified sequence of values. For Each...Next visits each object in a collection, such as each cell in a range. Use the counter form when the index or step matters, and the collection form when you simply need every item.

How do I loop through all used rows in Excel VBA?
Find the last populated row in a dependable column, then loop from the first data row to that row. For example, the worksheet-row example above uses column A to find the endpoint. Choose a column that is filled for every record you intend to process.

Can a For loop count backward in VBA?
Yes. Give Step a negative number, such as For i = 10 To 1 Step -1. Without a negative step, VBA does not infer that it should count down.

Why does my For loop skip the last row?
Check how the ending row is calculated and whether the code’s data column is populated in that row. Also check whether the loop’s step reaches the ending value; a step of 2 can pass over an endpoint that does not fit the sequence.

Can I undo a VBA loop with Ctrl+Z?
No. Excel generally clears its Undo history when a macro runs, so Ctrl+Z will not reverse the macro’s changes. Save a backup copy before running code that edits a workbook.

Can I run Excel VBA loops in a browser?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop version of Excel for Windows or Mac to use the Visual Basic Editor and run VBA code.

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.