Excel VBA: Loop Through Rows to the Last Row

VBA & macros · Updated November 21, 2026

To loop through data rows in Excel VBA, find the last nonempty cell in a reliable column, then use a For loop from the first data row to that row. For example, if column A contains a value on every record row and row 1 is a header, set the last row with ws.Cells(ws.Rows.Count, "A").End(xlUp).Row and loop from row 2 through that number.

The basic last-row loop

In a worksheet with headers in row 1 and data beginning in row 2, this pattern finds the final occupied cell in column A and visits each row through it. Replace the comment with the operation you need, such as checking a value, copying information, or applying a calculation.

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

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

    ' Visit each data row, leaving the header out.
    For r = 2 To lastRow
        ' Add the row-specific operation here.
        Debug.Print r, ws.Cells(r, "A").Value
    Next r
End Sub

The expression starts at the bottom of column A and behaves like pressing Ctrl+Up from the bottom of that column: it moves to the next nonempty cell above. Excel has a maximum worksheet row count, but using ws.Rows.Count avoids hard-coding that limit and works across supported worksheet sizes. Declaring lastRow as Long is important because a worksheet row number can exceed the range of VBA’s smaller Integer type.

Save a copy of the workbook before running a macro. VBA changes cannot be reversed with Ctrl+Z, so a backup is the straightforward way to recover from an unintended edit.

Choose the right column for finding the last row

The column used to determine the endpoint is often called the anchor column. It should contain a value in every row you intend to process. If column A holds an ID on every record, it is a good anchor. If some records have blank IDs but always have a date in column C, column C may be more reliable.

This choice matters when the data has gaps. Suppose rows 2 through 10 contain records, but A8 is blank. If A11 is also blank, the last nonempty cell in A is A7, and the basic loop will stop at row 7. Rows 8 through 10 will be skipped even if other columns contain data. Select a column that is consistently populated, or use a method that searches all columns when no such column exists.

  • Use an identifier column when every record is expected to have an ID.
  • Use a required date, name, or other always-present field when that is more dependable.
  • Do not use a column that is commonly blank near the bottom of the list.
  • Keep the anchor column and the column being processed conceptually separate: the loop can use one column to find its endpoint and read or write other columns.

If the data consists only of a header, the endpoint will be 1. A loop beginning at 2 and ending at 1 runs zero times, which is normally the desired result. If your first data row is different, change the loop’s starting number to match.

Always qualify the worksheet

Use a worksheet variable for both the endpoint calculation and the cells inside the loop. Code such as Cells(Rows.Count, 1).End(xlUp).Row without a worksheet qualifier can refer to whichever sheet is active. If the user changes sheets or another part of the macro activates a different sheet, the endpoint may come from one worksheet while the loop reads another.

The example uses ThisWorkbook.Worksheets("Data"), which means the workbook containing the VBA code and the sheet named Data. If the macro is stored in a separate personal macro workbook or add-in and should operate on the workbook currently in use, ActiveWorkbook may be the intended reference instead. Make that choice deliberately. A misspelled or missing sheet name raises a runtime error; it does not silently choose a similarly named sheet.

Qualify Rows.Count through the worksheet as shown. This makes clear that the row limit belongs to the same sheet used by Cells. Avoid relying on Select or Activate just to move through rows: direct references are easier to follow and are less sensitive to the active window or sheet.

Find the last used row anywhere on the sheet

When no single column is filled for every record, search for the last cell containing content across the worksheet. The Find method can search by rows from the bottom of the sheet upward. Specify its options because Excel can retain some Find settings from earlier searches, including searches made through the Find dialog.

Sub ProcessRowsUsingAnyColumn()
    Dim ws As Worksheet
    Dim foundCell As Range
    Dim lastRow As Long
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    ' Search formulas and values, starting from the bottom by row.
    Set foundCell = ws.Cells.Find(What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)

    If foundCell Is Nothing Then Exit Sub
    lastRow = foundCell.Row

    For r = 2 To lastRow
        ' Add the row-specific operation here.
        Debug.Print r
    Next r
End Sub

With LookIn:=xlFormulas, a formula counts as content even if its displayed result is an empty string. This is useful when formulas are part of the intended used data, but it can return a row below the visible values if formulas were filled farther down. If that is not what you want, choose a search approach that matches the actual definition of “last row” for your workbook. The code exits if the sheet contains no content at all; if it contains only a header, the loop still performs no data-row iterations.

Handle blank rows inside the data

A loop from the first data row to the last row processes every row number in between, including rows that are completely blank. That is usually helpful when blanks are occasional gaps: the row numbers remain aligned, and later records are still reached. Add a condition inside the loop if the operation should run only when a particular field is populated.

For example, to print only rows with a nonblank value in column A, put this inside the loop:

If Len(ws.Cells(r, "A").Value2) > 0 Then
    Debug.Print r, ws.Cells(r, "A").Value2
End If

Value2 reads the cell value without VBA’s Currency and Date conversions. The test above is suitable for ordinary text and numbers, but a cell containing an Excel error value needs an IsError check before applying string-oriented functions such as Len. Also decide whether a formula that returns an empty string should count as blank: its cell has a formula even though the displayed result looks empty.

Do not stop at the first blank row unless the data rules guarantee that everything below it is empty. A blank row in the middle is common in manually maintained worksheets, and an early exit would miss subsequent records.

Use Excel tables when the data is a structured list

An Excel table can be a clearer basis for a loop when the records are already formatted as a table. Tables expand as rows are added and provide a defined data body, so the code does not have to infer the list’s endpoint from a worksheet column. In VBA, a table is a ListObject; its DataBodyRange represents data rows without the header.

Before looping its rows, account for a table with no data rows: DataBodyRange can be Nothing in that case. This is a useful distinction from a simple sheet loop, where a header-only worksheet naturally results in zero iterations when the starting row is 2. Tables are especially useful when multiple lists occupy the same worksheet, since a worksheet-wide last-row search may include notes or unrelated content elsewhere.

UsedRange is another property people sometimes use to estimate the endpoint. It describes the worksheet’s used range, but formatting or prior editing can make that range larger than the visible data. It may therefore point to a row much farther down than the records. For a simple contiguous list, a known anchor column is generally more predictable; for a table, use the table; for scattered content, consider Find.

Loop direction and performance

A For r = 2 To lastRow loop moves from top to bottom. This is the natural choice when rows are processed independently or in their displayed order. To process bottom-up, use For r = lastRow To 2 Step -1. Bottom-up order is particularly useful when deleting rows, because deleting a row shifts the rows beneath it upward. If the loop moves downward while deleting, the next row can shift into the current position and be skipped.

For a modest list, reading or writing a few cells per iteration is usually straightforward. For a large list, repeatedly transferring individual cell values between VBA and the worksheet can become slow. A common optimization is to read a rectangular range into a VBA array, process the array in memory, and write results back in a batch. That approach adds complexity: array indexes are not worksheet row numbers, and the code must account for the range’s starting row and the possibility of a one-cell range.

Other speed improvements depend on what the macro does. Avoid selecting cells unnecessarily, and do not turn off calculation, events, or screen updating unless the macro needs it and restores each setting afterward, including when an error occurs. Changing those application-wide settings carelessly can affect other work in the Excel session.

Common errors and safe macro use

If the loop stops too early, first inspect the anchor column near the bottom of the data. A blank cell in that column may explain the result. If the loop runs too far, check for stray values, formulas, or formatting-related assumptions. The basic End(xlUp) expression detects nonempty cells in its chosen column; it does not know which rows a person considers part of the data.

  • Wrong sheet: qualify every range with the intended worksheet instead of relying on the active sheet.
  • Header processed as data: start at row 2 when row 1 is a header, or set the start row to the actual first record.
  • Rows missed after a gap: do not use a column with intermittent blanks as the endpoint anchor.
  • Macro will not run: save the workbook in a macro-enabled format such as .xlsm and use Excel’s desktop application. Excel for the web can open a workbook containing VBA but cannot run or edit its VBA macros.

On Windows, Alt+F11 opens the Visual Basic Editor in Excel. On Mac, VBA is available in desktop Excel, but keyboard shortcuts can depend on the keyboard and function-key settings; use the Visual Basic Editor command from the Developer tab or Excel’s Tools menu if the shortcut differs. The code examples use standard worksheet objects and do not rely on Windows-only features such as ActiveX controls.

Frequently Asked Questions

How do I loop through rows until the last row in column A?
Set lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, then loop from your first data row to lastRow. This assumes column A has a value in every record you need to include.

How do I find the last row if column A has blanks?
Use a different column that is always populated, or search the worksheet with Cells.Find using SearchOrder:=xlByRows and SearchDirection:=xlPrevious. The latter finds content anywhere on the sheet, which may include unrelated notes or formulas.

Why does my VBA loop stop at the wrong row?
The anchor column may contain a blank near the bottom, the code may be referencing a different active sheet, or a formula farther down may be affecting the result. Check the endpoint value in the debugger and verify which worksheet and column the code searches.

How do I skip blank rows in a VBA loop?
Keep the loop running through the last row, then wrap the row’s work in an If condition that checks a required field. This skips gaps without accidentally stopping before later records.

Can Excel for the web run this VBA macro?
No. Excel for the web can preserve and open a workbook that contains VBA, but running and editing VBA require desktop Excel. Use the desktop app to 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.