Excel VBA: Loop Through Rows in a Range Efficiently

VBA & macros · Updated November 21, 2026

To loop through every row in an Excel range with VBA, use For Each rowRange In dataRange.Rows when you want to process the rows in order. For ranges whose start and end rows are determined by worksheet data, use a numeric For loop and calculate the last row first.

The basic row loop

A range’s Rows collection gives you its individual rows. In the example below, dataRange covers rows 2 through 10 and columns A through C; the loop visits each of those nine worksheet rows once. The two Debug.Print expressions read the first and second cells within the current row range, which correspond to columns A and B here.

Save a copy of the workbook before running a macro. Although this example only prints values, VBA macros cannot be undone with Ctrl+Z, and later changes to a macro could affect worksheet data.

Sub InspectRows()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim rowRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A2:C10")

    ' Visit each row and print its first two values.
    For Each rowRange In dataRange.Rows
        Debug.Print rowRange.Cells(1, 1).Value, rowRange.Cells(1, 2).Value
    Next rowRange
End Sub

Change the worksheet name and range to match your workbook. The worksheet named Data must exist in the workbook containing this macro. ThisWorkbook means the workbook where the VBA code is stored; it is not necessarily the workbook currently selected in Excel. To view the printed output, open the Immediate window in the Visual Basic Editor.

Choose For Each or a counter loop

Use For Each when your task naturally applies to each row: inspect values, test a condition, or update a cell in the row. It avoids manually calculating row numbers and is easy to adapt if the range changes. The loop variable must be an object variable such as a Range, declared with Dim, because each item returned by dataRange.Rows is itself a range.

A counter loop, such as For rowNum = 2 To lastRow, is useful when you need the worksheet row number, want to move in reverse, or need to control the step size. It also makes it straightforward to refer directly to worksheet cells using Cells(rowNum, columnNumber). In either pattern, use Long for row numbers; Excel worksheets can contain far more rows than fit in the range of the older VBA Integer type.

For a fixed range, you can choose either pattern. The For Each version emphasizes the row objects; the counter version emphasizes row indexes. Avoid changing the structure of a collection while iterating over it—for example, deleting rows as you move forward—because later items can shift and be skipped. If you need to delete worksheet rows, loop from the bottom upward or collect the rows to remove before deleting them.

Find the last row safely

Real lists often grow, so a fixed endpoint such as row 10 can miss new records. A common approach is to find the last nonblank cell in a column that is reliably filled for every record. The following example assumes headers in row 1, record identifiers in column A, amounts in column B, and a results column C. It labels amounts of 1000 or more as “Review.”

Save a copy of the workbook before running this macro. It writes to column C, and macro changes cannot be reversed with Ctrl+Z. If your records do not start on row 2, adjust the starting row and the header check accordingly.

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

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

    If lastRow < 2 Then Exit Sub

    For rowNum = 2 To lastRow
        If IsNumeric(ws.Cells(rowNum, "B").Value) Then
            If ws.Cells(rowNum, "B").Value >= 1000 Then
                ws.Cells(rowNum, "C").Value = "Review"
            End If
        End If
    Next rowNum
End Sub

ws.Rows.Count refers to the number of rows on that worksheet, and End(xlUp) moves upward from the bottom of column A to its last populated cell. This is often more dependable than relying on a selection or the active cell. The result is only as good as the column you choose: if the last records can have blank identifiers in A, use another consistently populated column. If column A contains stray notes below the data, the calculated endpoint will include those rows too.

The macro leaves column C unchanged for amounts below 1000 or for nonnumeric values. If you want to clear old labels when a row no longer qualifies, add an explicit Else branch that clears the result cell. Do that only if clearing existing values is intended.

Handle headers, tables, and changing data

Most worksheet lists have a header row. Start at row 2 when row 1 contains column names, as in the dynamic example. If there is no header, start at row 1. This small decision prevents a macro from treating a heading such as “Amount” as a data value. For a range that is passed into a procedure, you can also set the range to exclude the header before the loop begins.

For data formatted as an Excel Table, a table’s data body excludes its header row. Tables are useful when a list grows because they expand as rows are added and keep column headings attached to the data. VBA can refer to a table through a ListObject and its DataBodyRange. Be aware that an empty table may have no data body range, so check that it is not Nothing before trying to loop over it. This avoids an error when the table contains headings but no records.

Keep the worksheet and range explicit rather than relying on ActiveSheet or Selection. Those references can point somewhere unexpected if the user clicks another sheet or workbook before running the macro. Explicit references make a routine more predictable and easier to review. For a reusable procedure, pass a Range argument into it rather than embedding a particular sheet and address in the code.

Improve speed when processing many rows

For a few hundred rows, reading and writing cells inside a loop is usually straightforward and sufficiently clear. With tens of thousands of rows or more, repeated communication between VBA and the worksheet can become a bottleneck. A common optimization is to read a contiguous range into a VBA array, process the values in memory, and write the results back in one operation. This reduces the number of worksheet read and write operations.

Optimization should not make the routine harder to verify without a reason. First ensure the loop uses a bounded range rather than scanning entire columns unnecessarily, and avoid selecting or activating cells on every pass. If calculation or screen updates are temporarily disabled for a long task, restore their prior settings even if an error occurs; otherwise Excel may be left in an unexpected state. For ordinary-sized lists, a clear loop with direct cell references is often the better trade-off.

There is also an important distinction between looping over rows and looping over cells. A row loop is convenient when several columns belong to one record—for example, checking an order amount and writing a status beside it. A cell loop over one column may be simpler when every value is handled independently. Pick the smallest range that contains the information the operation actually needs.

Common problems and how to fix them

  • Subscript out of range: Check that the worksheet name is spelled exactly as it appears on the sheet tab and that the referenced workbook is the intended one.
  • The loop misses the final record: Confirm that the last-row calculation uses a column populated on the final record. A blank in the chosen column can make the endpoint too early.
  • The header is processed as data: Start the loop below the header, commonly at row 2, or define a data-only range.
  • Rows are skipped after deletion: Deleting a row shifts subsequent rows upward. Iterate from the last row toward the first, or mark rows first and remove them in a separate pass.
  • Nothing happens when the macro runs: Check the worksheet name, range address, starting row, and condition. In the example, a value must be numeric and at least 1000 to produce a label.
  • A type mismatch appears: Cells may contain text, blanks, or error values where the code expects a number. Validate values before doing numeric comparisons and decide how each unusual value should be handled.

One frequent misconception is that Rows(2) always means worksheet row 2. When used on a range, row indexes are relative to that range: the first row of Range("B5:D8").Rows is worksheet row 5. Similarly, rowRange.Cells(1, 1) means the first cell inside that row range, not necessarily cell A on the sheet. Keeping these relative references in mind prevents many off-by-one and wrong-column mistakes.

Run VBA on the right Excel platform

These examples are for desktop Excel for Microsoft 365 on Windows. Press Alt+F11 to open the Visual Basic Editor, insert a standard module, and place the macro there. On Mac, Option+F11 commonly opens the editor; some keyboards require the Fn key as well. The exact keyboard behavior can depend on the Mac keyboard and its function-key settings.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm). 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. Do not enable macros indiscriminately: only run code from a source you trust, and follow your organization’s security requirements. Before any macro that changes or deletes data, keep a separate copy of the workbook because Ctrl+Z does not undo VBA actions.

If the job is simply to calculate or mark rows, formulas, filters, or conditional formatting may be enough and do not require a macro. VBA is most useful when the process involves repeated multi-step actions, custom decisions, or applying the same operation across sheets. Choose the simplest tool that fits the task and the workbook’s security constraints.

Frequently Asked Questions

How do I loop through every used row in Excel VBA?
Find the last row in a consistently populated column, then use a counter loop from the first data row through that endpoint. This adapts to additions, but it may include stray values below the list, so choose the endpoint column carefully.

How do I skip the header row?
Set the loop’s first row to the first data row, usually row 2 when row 1 contains headings. For an Excel Table, use its data body rather than its header range, and check for an empty data body before looping.

Can I use For Each to loop through rows?
Yes. Declare a variable as Range and use For Each rowRange In dataRange.Rows. Each pass supplies one row range, whose cells can be read or changed using relative indexes.

Why is my VBA loop only processing one row?
Check that the range passed to the loop actually contains multiple rows and that the loop’s start and end values are correct. Also confirm that an Exit For statement or an early procedure exit is not stopping the loop.

Can I loop through rows in Excel for the web?
Excel for the web does not run VBA macros. Open the workbook in desktop Excel to run the examples, or consider formulas and other web-compatible tools if the task does not require VBA.

Related

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.