Excel VBA Loop Through Used Rows: Safe, Fast Methods

VBA & macros · Updated November 21, 2026

To loop through rows Excel considers used, set a range to the worksheet’s UsedRange and iterate over its rows. For a regular data table, a loop from the header row to the last populated row in a reliable key column is usually more predictable; the examples below show both approaches and explain when to use each.

What Excel means by used rows

A worksheet’s used range is the rectangular area Excel recognizes as having been used. It usually includes cells with data or formulas, but formatting can also affect its boundaries. For example, if a cell far below your table was formatted and later cleared, Excel may still treat the worksheet as used down to that row. As a result, UsedRange.Rows.Count is not always the number of data rows, and the range may begin somewhere other than row 1.

The distinction matters because a loop needs both a starting row and an ending row. If the used range starts at row 5 and extends through row 20, its row count is 16, but its worksheet row numbers are 5 through 20. A loop from 1 to 16 would therefore use the wrong rows. The examples account for the range’s starting row.

Also distinguish a used row from a meaningful record. A row may be inside the used range but contain no data in the columns you care about. Conversely, a formula that displays an empty string can still make a cell count as occupied. Choose a loop boundary and a row test that match the structure of your data.

Loop through every row in UsedRange

Use this pattern when you genuinely want to visit every row in the worksheet’s current used range. It prints each worksheet row number to the Immediate window in the VBA editor; replace the marked line with the work your macro needs to perform.

Sub VisitUsedRows()
    Dim ws As Worksheet
    Dim usedRng As Range
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    Set usedRng = ws.UsedRange

    ' Visit each worksheet row covered by the used range.
    For r = usedRng.Row To usedRng.Row + usedRng.Rows.Count - 1
        Debug.Print r
        ' Add row-specific work here.
    Next r
End Sub

usedRng.Row gives the worksheet row number of the first row in the range. Adding the row count and subtracting one gives the final worksheet row number. The loop variable is declared as Long, which is appropriate for Excel worksheet row numbers. The worksheet is named explicitly so the macro does not accidentally act on whichever sheet happens to be active.

If you need to inspect the used cells in each row rather than just the row number, refer to the corresponding cells in usedRng, or use a known column range such as ws.Cells(r, "A"). Remember that the used range can span many columns; an entirely empty row within that rectangle is still visited by this loop.

Choose a last-row method that fits the data

For a table with a dependable identifier in column A, such as an order number that is present on every record, finding the last non-empty cell in that column is often the clearest method. With headers in row 1, this macro processes rows 2 through the last occupied cell in column A:

Sub MarkRecords()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Start at row 2 because row 1 contains headers.
    For r = 2 To lastRow
        If Len(ws.Cells(r, "A").Value2) > 0 Then
            ws.Cells(r, "D").Value = "Reviewed"
        End If
    Next r
End Sub

This is a good choice when column A is reliably filled for every record. It is not a safe general-purpose way to find the last row if records can have blanks in column A or if other columns contain data below the final value in A. In that case, choose a different dependable column or search the worksheet for the last populated cell.

A worksheet-wide search can be useful when data is spread across columns. The VBA Find method remembers some settings from earlier searches, so specify the important arguments each time. For example, assign the result of a search for * to a Range variable, searching by rows in the previous direction to locate the last matching cell. Check that the result is not Nothing before reading its row number, because a completely empty sheet has no match. Use LookIn:=xlFormulas to include cells containing formulas, including formulas whose displayed result is blank; use LookIn:=xlValues if the displayed values are the relevant criterion.

There is no single best boundary method for every workbook. Use UsedRange to examine the worksheet’s recognized used area, a key-column lookup for a well-structured table, or a worksheet-wide search when records do not have one dependable anchor column.

Skip blank rows and check the right cells

Finding the bottom boundary does not decide whether every row between the top and bottom is a record. If a table has occasional blank lines, add a condition before processing. The example above checks column A with Len; it skips an empty cell but accepts text and numbers converted to a string. If column A contains formulas returning an empty string, Len returns zero for their displayed result, so those rows are skipped.

When a record is defined by several columns, test the relevant range rather than a single cell. Application.CountA counts non-empty cells, including formulas that return an empty string. That may be exactly what you want if a formula means the row is populated, but it differs from checking what a person can see. For more nuanced rules, test the specific fields that make a row valid, such as an ID and a date, instead of treating every nonblank cell as proof of a complete record.

Blank rows inside a dataset do not stop a numeric For loop. The loop simply visits them, and your condition determines whether to skip or process them. Do not assume that End(xlUp) finds the first gap; it finds the last non-empty cell above the bottom of the specified column.

Looping through a filtered list or Excel Table

A standard row-number loop visits worksheet rows even when a filter hides some of them. If your task should apply only to visible rows, visibility needs to be part of the logic. One option is to test ws.Rows(r).Hidden before processing each row. Be clear about whether manually hidden rows should also be excluded, since this property concerns hidden rows generally, not just rows hidden by an AutoFilter.

For data formatted as an Excel Table, a ListObject can be a better starting point than the worksheet’s used range. A table has a defined data body and named columns, so code can refer to the table’s rows rather than unrelated cells elsewhere on the sheet. Check whether the table has a data body before trying to loop it: a table with headers but no records may have no data rows. Table-based code also makes the intended dataset easier to identify when the worksheet contains notes, summaries, or multiple blocks.

Improve performance on large worksheets

A row-by-row loop is easy to understand and is often sufficient for ordinary workbooks. It can become slow when it makes many separate reads from or writes to worksheet cells, because each cell access involves communication with Excel’s worksheet object model. For a large contiguous block, a common improvement is to read the range’s values into a VBA array, process the array in memory, and write results back in one operation.

That approach is most useful when the data is rectangular and the macro needs to examine or update many cells. It takes more care: array indexes may not match worksheet row numbers, a one-cell range can return a different value shape than a multi-cell range, and writing a whole array back can overwrite cells you did not intend to change. Start with a straightforward loop, confirm the correct boundaries and results, and optimize only when runtime is a real concern.

For macros that update many cells, temporarily turning off screen updating can reduce visual refresh work. If you change application settings such as Application.ScreenUpdating or calculation mode, restore the original setting even if an error occurs. A macro that exits early without restoring settings can leave Excel behaving unexpectedly. Avoid adding these changes automatically to a small loop that does not need them.

Common mistakes and how to prevent them

  • Using the range count as the final worksheet row. The count is a size, not necessarily a row number. Add it to the range’s starting row and subtract one, or use a method that returns the final row directly.
  • Assuming UsedRange matches the visible table. Formatting and stray content can enlarge it. Inspect the actual range and use a table or a known data column when that better represents the records.
  • Using the active sheet by accident. Qualify references with a worksheet variable, as in ws.Cells, instead of relying on ActiveSheet or unqualified Cells.
  • Including the header in data processing. Set the first loop row deliberately. If there are multiple header rows, account for all of them.
  • Assuming a blank-looking formula cell is empty. Decide whether formulas returning an empty string count as data, then select a test that reflects that decision.
  • Using an Integer for row numbers. Excel worksheets can have more rows than a VBA Integer can represent. Use Long.

Run VBA safely on Windows and Mac

Before running a macro that changes workbook cells, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is the simplest way to recover from an unintended bulk edit. Review the worksheet name, starting row, last-row logic, and columns being changed before running it.

In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11. On a Mac, the shortcut may be Option+F11 or Fn+Option+F11, depending on the keyboard and function-key settings; you can also open the Visual Basic editor from Excel’s Developer tab. To insert a standard module in the editor, use Insert > Module, paste the procedure, and run it from the editor. If the Developer tab is not visible, enable it through Excel’s ribbon settings.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the macro. Excel for the web can open and preserve a macro-enabled workbook, but it cannot create, edit, or run VBA macros. The desktop Excel app is required for those tasks. Macros may also be restricted by your organization’s security settings; only enable macros in files you trust.

Frequently Asked Questions

How do I loop through all used rows in VBA? Set a range variable to ws.UsedRange, then loop from its Row through Row + Rows.Count - 1. This accounts for a used range that begins below row 1.

What is the best way to find the last row in Excel VBA? If every record has a value in a known column, use Cells(Rows.Count, column).End(xlUp).Row with worksheet references qualified. If no column is dependable, search for the last populated cell across the worksheet and check for a missing result.

Why does UsedRange include blank rows? The used range can include cells affected by formatting or earlier worksheet use, and it is a rectangular range. Rows inside that rectangle may be blank in the columns you consider data. Add a row-level condition or use a better-defined table boundary.

Does a VBA row loop skip filtered-out rows? No. A normal loop over row numbers can visit filtered or manually hidden rows. Test whether each row is hidden if the macro should process only visible rows, and decide how manually hidden rows should be handled.

Can I run an Excel VBA loop in Excel for the web? No. Excel for the web does not run VBA. Open the workbook in the desktop version of Excel for Windows or Mac to use the VBA editor and run the macro.

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.