VBA Loop Code: Examples for Excel Macros

VBA loop code repeats an instruction for a set number of times, for each item in a collection, or while a condition remains true. For a simple count, use a For...Next loop; save a copy of your workbook before running any macro, because VBA changes cannot be undone with Ctrl+Z.

Sub CountToFive()
    Dim counter As Long

    ' Print the numbers 1 through 5 in the Immediate window.
    For counter = 1 To 5
        Debug.Print counter
    Next counter
End Sub

On Windows, open the Visual Basic Editor with Alt+F11, add a standard module through Insert > Module, and put the macro there. In the editor, Ctrl+G opens the Immediate window, where this example prints its results. On a Mac, Option+F11 opens the editor on many keyboards; you may need to hold Fn as well, depending on keyboard settings. Excel for the web does not run VBA macros. The examples below are for desktop Excel, and assume ordinary worksheets without unusual workbook protection or error values in the cells being processed.

How VBA loops work

A loop runs a block of statements repeatedly. The loop’s control rule determines when it starts, how it advances, and when it stops. A counted loop has a starting value and an ending value. A collection loop takes each item in turn. A condition-based loop continues until a test changes. Choosing a rule that matches the task makes code easier to understand and reduces the chance of an endless loop.

In VBA, a variable used to count iterations is commonly declared as Long. This whole-number type is suitable for worksheet row numbers and ordinary counters. A variable representing a worksheet cell can be declared as Range, while a worksheet variable uses the Worksheet type. Declaring variables makes the intended role of each one clearer and helps catch misspellings when the module uses Option Explicit.

To run a macro, place the cursor inside its procedure in the Visual Basic Editor and press F5, or use Excel’s macro controls. Save a working copy first: macros can overwrite values, delete data, or change formatting, and Excel’s Undo command does not reverse those changes. Store macro-enabled workbooks as .xlsm files if you need the VBA code to remain in the workbook.

Use For…Next for a known number of repetitions

For...Next is the clearest choice when the number of iterations is known or can be calculated before the loop begins. The counter starts at the first value, advances by one by default, and stops after reaching the ending value. Both endpoints are included. The previous example therefore prints five numbers, not four.

Add Step when the counter should advance by a different amount. For example, this macro prints the even-numbered values from 2 through 10:

Sub PrintEvenNumbers()
    Dim counter As Long

    ' Advance by two on each iteration.
    For counter = 2 To 10 Step 2
        Debug.Print counter
    Next counter
End Sub

The output is 2, 4, 6, 8, and 10. A negative step counts downward: a loop from 10 to 1 with Step -1 visits 10, 9, and so on through 1. Make sure the step direction agrees with the start and end values; otherwise the loop may not run as intended. Use a counter variable that is not also changed unexpectedly inside the loop, because changing it manually can make the sequence difficult to follow.

Counted loops are useful for tasks such as repeating an operation over a fixed number of worksheets, generating numbered labels, or processing a known range of rows. If the number of items may change, calculate the ending value from the worksheet rather than hard-coding it, or consider a collection loop.

Use For Each to visit every cell or object

For Each...Next is often the natural option when the goal is to handle every member of a range or collection. It avoids maintaining a separate numeric position counter. This example assumes the text to convert is in A2:A6 on a sheet named Sheet1; it writes uppercase versions into the adjacent cells in column B and leaves the original text in column A unchanged.

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

    ' Read names from A2:A6 and write results to column B.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    For Each cell In ws.Range("A2:A6")
        If Len(cell.Value2) > 0 Then
            cell.Offset(0, 1).Value = UCase$(CStr(cell.Value2))
        End If
    Next cell
End Sub

For example, if A2 contains “Riley” and A3 contains “Morgan,” B2 becomes “RILEY” and B3 becomes “MORGAN.” Empty cells are skipped. This example is intended for text entries without Excel error values; an error value in the source range needs separate handling before it is converted to a string. The output cells are overwritten, so check that column B is available before running the macro.

The loop variable in a For Each loop must be compatible with the objects being visited. Use Range for worksheet cells, for example. You can also use this pattern with worksheets in a workbook or other VBA collections. It is usually more readable than using a numeric index when the position itself does not matter.

Use Do While or Do Until for condition-based work

Use a Do loop when the stopping point depends on a condition rather than a fixed count. A condition at the top is checked before each pass, so the body may run zero times if the condition is false at the outset. This example repeats five times because the counter begins at 1 and increases each time:

Sub CountWithDoWhile()
    Dim counter As Long

    ' Continue while the counter is at most five.
    counter = 1
    Do While counter <= 5
        Debug.Print counter
        counter = counter + 1
    Loop
End Sub

Do Until condition uses the opposite sense: the loop continues until the condition becomes true. For example, Do Until counter > 5 can perform the same count as the previous example if the counter is initialized and advanced correctly. VBA also supports placing While or Until after Loop. That form tests the condition at the bottom, so the body runs at least once. Prefer a top-tested form when the body should be allowed to run zero times.

Every condition-based loop needs a reliable way to reach its stopping condition. If a counter never changes, or a cell value is never updated, the condition may remain true forever. When a loop depends on worksheet data, check that each pass moves to another row or changes the value being tested. For open-ended tasks, include a clear exit condition and consider a reasonable maximum iteration count as a safeguard.

Loop through worksheet rows safely

A common Excel task is to process rows from a header down to the last used entry. The next macro assumes that column A has a header in A1 and contains the records to check. It finds the last nonempty cell in column A and writes “Checked” in column B for each data row. Existing values in B will be replaced.

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

    ' Find the last nonempty row in column A.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Mark each data row; row 1 is the header.
    For rowNum = 2 To lastRow
        If Len(ws.Cells(rowNum, "A").Value2) > 0 Then
            ws.Cells(rowNum, "B").Value = "Checked"
        End If
    Next rowNum
End Sub

If there are no records below the header, the loop has a start value of 2 and an end value of 1, so a positive-step For loop does not execute. The last-row method finds the last nonempty cell in column A, not necessarily the last row used anywhere on the worksheet. If records are identified by another column, change the column letter in the last-row calculation to match the data layout.

Using ThisWorkbook means the workbook containing the macro. This is often safer than ActiveWorkbook, which refers to whichever workbook currently has focus and may not be the intended file. Qualifying the worksheet and range references also avoids depending on whichever sheet happens to be active.

Choose the right loop and avoid common errors

Use the loop type that expresses the task directly:

For…Next: choose it for a known count, a row-number range, or a sequence that advances by a fixed step.
For Each…Next: choose it when you need to visit each cell, worksheet, or other object in a collection.
Do While or Do Until: choose it when the stopping point depends on a condition that can change while the macro runs.

Common problems often come from a small mismatch between the loop rule and the data. Check these points when code skips work, runs too many times, or appears stuck:

Off-by-one ranges: remember that For counter = 1 To 5 includes both 1 and 5. For worksheet data with a header in row 1, data often begins at row 2.
Wrong worksheet: use a workbook and worksheet reference rather than relying on the active sheet.
Unchanged condition: update the counter or worksheet value that controls a Do loop.
Overwritten cells: confirm that destination cells are clear or that replacing their contents is intended.
Unexpected cell contents: blank cells, text, and Excel errors may behave differently from ordinary numbers. Add checks for the data types your workbook can contain.

For a loop that seems not to stop, use the Visual Basic Editor’s Reset button to stop execution, then inspect the condition and the statement that should change it. You can also step through code with F8 and watch the counter or row number change. These debugging tools help reveal whether the loop starts at the wrong place, fails to update its control variable, or encounters unexpected data.

Speed, safety, and alternatives

For a few dozen cells, a straightforward loop is usually easy to maintain. For thousands of cells, reading and writing one cell at a time can be slower because each interaction crosses between VBA and Excel’s worksheet object model. A common optimization is to read a range into a VBA array, process the array in memory, and write the results back in one operation. That approach takes more code and should be used only when performance matters; it also requires careful handling of the range’s dimensions and data types.

Do not disable Excel’s security protections just to make a macro run. Run VBA only from a source you trust, and follow your organization’s macro policy. Macros can change or remove workbook data, and Ctrl+Z will not undo their actions. A separate backup is more dependable than relying on Undo or on the current workbook remaining unchanged.

For a task that can be done with worksheet formulas, formulas may be easier to audit and update than a macro. For automation in Excel for the web, Office Scripts are an alternative in supported Microsoft 365 accounts, but they use TypeScript rather than VBA. Excel for Mac supports VBA, although some Windows-specific features, such as Windows API calls and COM automation, are not available in the same way. Keep macros based on standard Excel objects when cross-platform use matters.

Frequently Asked Questions

What is the simplest VBA loop code?
A For...Next loop is a simple starting point when you know the number of repetitions. Declare a counter variable, set its start and end values, put the repeated statements between For and Next, and check that the endpoints match the task.

How do I loop through every used row in Excel VBA?
Find the last nonempty row in a reliable key column with Cells(Rows.Count, column).End(xlUp).Row, then use a For...Next loop from the first data row to that row. Qualify the worksheet references so the code uses the intended sheet.

What is the difference between For and For Each in VBA?
For...Next uses a numeric counter, so it suits a known sequence or row numbers. For Each...Next visits each object in a collection, such as every cell in a range, without requiring you to manage an index.

How do I stop an infinite VBA loop?
In the Visual Basic Editor, use the Reset button to stop a running macro. Then verify that the loop’s condition can become false and that each pass updates the counter or data that controls it. Save a copy before rerunning code that changes worksheet contents.

Can I run VBA loops in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel to run VBA, or consider Office Scripts for compatible web-based automation in supported Microsoft 365 environments.

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.