Excel Macro For Loop: Examples and Common Fixes

An Excel macro for loop is a VBA loop that repeats instructions for a set of items or a known number of times. In the VBA editor, use a For...Next loop, such as For i = 2 To 6, to run the same action on rows 2 through 6; the example below writes a 10% increase from column A into column B.

What a For loop does in an Excel macro

A loop saves you from writing the same instruction repeatedly. Instead of assigning a result to five cells with five separate lines, a macro can use one instruction inside a loop and change the row number each time. This is useful for repeated work such as checking cells, applying formatting, copying values, or calculating results across a list.

In VBA, the basic pattern is For counter = start To end, followed by the instructions to repeat, then Next counter. The counter is a variable: it begins at the start value, usually increases by one after each pass, and the loop ends after it has processed the end value. Both ends are included. For example, a loop from 2 to 6 runs five times: once each for 2, 3, 4, 5, and 6.

The word macro refers to the VBA procedure that Excel runs. The loop is one part of that procedure; it does not run by itself until you start the macro. The example uses rows 2 through 6 on the active worksheet and assumes those cells in column A contain numbers.

A working For…Next example

This macro multiplies each value in A2:A6 by 1.1 and puts the result in the same row of column B. If column A contains 10, 20, 30, 40, and 50, column B receives 11, 22, 33, 44, and 55. The macro overwrites any existing values in B2:B6, so check those cells first.

Option Explicit

Sub AddTenPercent()
    Dim ws As Worksheet
    Dim i As Long

    Set ws = ActiveSheet

    ' Assumes numeric source values in A2:A6.
    For i = 2 To 6
        ws.Cells(i, 2).Value = ws.Cells(i, 1).Value * 1.1
    Next i
End Sub

i is the row counter, and ws holds a reference to the worksheet. Cells(i, 1) means the cell at row i, column 1, which is column A; column 2 is B. Declaring i as a Long is a standard choice for row counters because worksheets can contain far more rows than fit in the smaller Integer range. Option Explicit asks VBA to require variable declarations, helping catch misspelled variable names.

Where to put the macro and how to run it

Save a copy of the workbook before running a macro. VBA changes cannot be reversed with Ctrl+Z, so a separate copy gives you a way back if the result is wrong.
In Excel for Microsoft 365 on Windows, show the Developer tab if needed through File > Options > Customize Ribbon, then select Developer. Choose Developer > Visual Basic, or press Alt+F11.
In the Visual Basic Editor, choose Insert > Module. Paste the procedure into the new module. A standard module is a suitable place for a macro you intend to run directly.
Return to Excel and save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). A regular .xlsx file does not retain VBA macro code.
Run it from Developer > Macros: select AddTenPercent and choose Run. You can also run a procedure from the Visual Basic Editor with the cursor inside it by pressing F5.

On a Mac, Excel desktop supports VBA, though keyboard shortcuts can vary with the keyboard and function-key settings. Use Developer > Visual Basic if available, or open the Visual Basic Editor through Excel’s macro tools; Option+F11 may require the Fn key on some keyboards. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Use desktop Excel for those tasks.

Looping through rows that change

A fixed end value is easy to understand, but a list may gain or lose rows. In that case, find the last used row in the source column before starting the loop. This version processes column A from row 2 down to the last non-empty cell in that column and writes the 10% increase in column B.

Option Explicit

Sub AddTenPercentToList()
    Dim ws As Worksheet
    Dim i As Long
    Dim lastRow As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Process the data rows, leaving row 1 as a heading.
    For i = 2 To lastRow
        ws.Cells(i, 2).Value = ws.Cells(i, 1).Value * 1.1
    Next i
End Sub

ws.Rows.Count identifies the bottom row of the worksheet, and End(xlUp) moves upward from the bottom of column A to its last non-empty cell. This common technique assumes column A is the column that determines where the data ends. If that column has gaps near the bottom, the last populated cell may not match the final row in another column. Pick a reliable column for the data boundary, and decide what the macro should do when the sheet has no data below the heading.

For Each, Step, and early exits

Use For...Next when the counter or row number matters. Use For Each when you want to perform an action on each object in a collection, such as every cell in a range. For example, this procedure colors non-empty cells in A2:A6. It processes the cell objects directly instead of tracking row numbers.

Option Explicit

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

    Set ws = ActiveSheet

    ' Visit each cell in the stated range.
    For Each cell In ws.Range(ws.Cells(2, 1), ws.Cells(6, 1))
        If Len(cell.Value) > 0 Then
            cell.Interior.Color = vbYellow
        End If
    Next cell
End Sub

A regular For...Next loop increases its counter by 1 unless you specify a step. For example, For i = 10 To 2 Step -2 visits 10, 8, 6, 4, and 2, which is useful for working backward or skipping rows. A negative step needs a starting value greater than or equal to the ending value. If the counter begins beyond the end in the direction of travel, the loop does not run.

Exit For ends the current loop early. It can be useful when a search finds the item it needs, but it should be used deliberately: the macro will stop visiting the remaining items. For a search, keep track of whether a match was found so the code can distinguish a successful early exit from reaching the end without a match.

Choosing between For and Do loops

Choose For...Next when you know the number of passes or have a clear beginning and ending counter. It is a natural fit for processing a specified range of rows, repeating an action ten times, or counting through a series of columns. If you use a variable last row, the number of passes is determined before the loop begins.

A Do While or Do Until loop is more appropriate when repetition should continue until a condition changes, and the number of passes is not known in advance. For example, code might continue looking through entries until it reaches a blank cell. With a condition-controlled loop, make sure something inside the loop changes the condition. If not, the macro can repeat indefinitely until it is interrupted.

For a known number of repetitions, a For...Next loop makes the stopping rule visible in one place. For a condition-based task, a Do loop can express the intent more clearly. Neither is automatically faster or safer in every situation; selecting the loop that matches the task makes the code easier to review and maintain.

Common errors and ways to prevent them

Using the wrong row bounds: If the first data row is 2 but the loop starts at 1, it may process a heading. If the end row is too small, it silently skips records. Confirm which rows contain headings and data before running the macro.
Overwriting existing work: A loop that writes to column B replaces the contents of those cells. Choose a blank output column, make a backup, or adapt the macro so it checks the destination before writing.
Using an unsuitable last-row column: A last-row calculation based on column A cannot reliably describe a list whose column A is blank in the final records. Use a column that is consistently populated.
Unexpected data types: Multiplying text or an error value is not the same as multiplying a number. If a range may contain blanks, text, or worksheet errors, add validation and decide whether to skip, clear, or report those rows.
Forgetting the worksheet context: ActiveSheet means whichever worksheet is active when the macro runs. If that could be the wrong sheet, refer to a known worksheet deliberately, and ensure the workbook contains that sheet.
Changing the counter inside the loop: Avoid assigning a new value to the counter inside the loop unless the altered sequence is intentional. It can make the loop skip rows or behave in a way that is difficult to follow.

When debugging, step through the procedure in the Visual Basic Editor with F8 on Windows and watch the highlighted line and variable values. A quick check of the input range, output range, start and end values, and active worksheet often reveals why a macro produced an unexpected result.

Macro safety, saving, and compatibility

VBA can change many cells quickly, which is why previewing the target range matters. Run unfamiliar code only when you understand what it changes and trust its source. Excel’s macro security settings and prompts are there to limit unsafe code; do not enable all macros globally just to make one workbook run. If a file came from another person or the internet, follow your organization’s security guidance before enabling its macros.

Keep the original workbook or a separate backup before running code that edits data. A macro’s changes generally cannot be undone with Ctrl+Z. Save the code in an .xlsm workbook if you need to retain it. If you share the workbook with someone who does not need the code, consider providing a separate data-only copy, and make clear which file contains executable macros.

Excel for Mac supports many VBA procedures, but Windows-specific features, external libraries, ActiveX controls, and operating-system calls may not work the same way or may not be available. The simple worksheet loops shown here use ordinary Excel objects and are more portable than code that depends on those features. Excel for the web does not execute VBA, so recipients need desktop Excel to run these macros.

Frequently Asked Questions

How do I write a For loop in an Excel macro?
In a VBA procedure, write For counter = start To end, put the repeated instructions beneath it, and close it with Next counter. The start and end values are included.

How do I loop through all used rows in Excel VBA?
Find the last used row in a column that is reliably filled for each record, then use that row number as the end value in a For...Next loop. A last-row calculation based on a column with gaps may stop too early.

What is the difference between For and For Each in VBA?
For...Next repeats using a counter, which is convenient when row or column numbers matter. For Each visits each object in a collection, such as each cell in a range.

Can I run an Excel VBA macro in the browser?
No. Excel for the web can open macro-enabled workbooks, but VBA macros must be created, edited, and run in desktop Excel.

Can I undo a macro with Ctrl+Z?
Usually not. VBA actions generally clear Excel’s normal undo history, so save a separate copy before running a macro that changes 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.