Excel VBA AutoFilter Last Row: Find the Right Range

VBA & macros · Updated November 21, 2026

To include every record in an Excel VBA AutoFilter, find the last data row and build the filter range from the header row through that row. If column A is filled for every record and headers are in row 1, use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row; the result is the row number to use as the range’s lower boundary. This approach is simple, but it depends on column A being a dependable marker for the end of the data.

Why the last row matters

An AutoFilter applies to a rectangular range that includes the column headings and the records beneath them. VBA’s Range.AutoFilter method does not automatically know which rows belong to your data if you give it an incomplete or overly broad range. A fixed range such as A1:F100 can miss later records when the list grows, while a range extending to the bottom of the worksheet can include thousands of empty rows.

The last-row calculation determines where the filter range stops. If it stops too soon, new records remain outside the dropdown filter and will not be hidden or shown with the rest of the list. If it reaches too far, blank rows may be included, and data elsewhere on the sheet could be caught in the filter range. The goal is not simply to find the last nonempty cell anywhere; it is to identify the actual bottom of the dataset you intend to filter.

AutoFilter hides rows that do not match the chosen criteria; it does not delete them. A correct range therefore matters both when the macro applies a criterion and when a person later changes or clears that criterion.

Choose a dependable way to find the last row

The usual method starts at the bottom of a worksheet column and moves upward to the first occupied cell. For example, with headers in row 1 and a value in column A for every data record, the calculation returns the last occupied row in column A. This is generally fast and easy to understand, and it keeps working as records are added or removed.

Choose the column carefully. A transaction ID, date, or other required field is often a better anchor than an optional comment or status column. A blank cell in the anchor column below the real data can make the result stop early, even if other columns have values on that row. Conversely, a stray note far below the list in the anchor column can make the result too large.

  • Use a key column when every valid record must have a value in that column.
  • Use a table when the data is a structured Excel Table; its range expands as records are added.
  • Search across the worksheet or a defined data area when no single column is consistently populated, while taking care not to include unrelated notes.

There is no universal last-row formula that can infer which cells are conceptually part of a list. The workbook’s layout and rules for a valid record determine the right method.

Build a filter range with VBA

This example assumes the worksheet is named Data, the header row is row 1, every record has a value in column A, and the desired filter field is the third column of the range. It applies the criterion Open. Before running a macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z.

Option Explicit

Sub FilterDataThroughLastRow()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim filterRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Assumes row 1 has headers and column A marks every record.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    ' Do nothing if there are no records or fewer than three fields.
    If lastRow < 2 Or lastCol < 3 Then Exit Sub

    Set filterRange = ws.Range(ws.Cells(1, 1), _
                               ws.Cells(lastRow, lastCol))

    ' Field 3 means the third column within filterRange.
    filterRange.AutoFilter Field:=3, Criteria1:="Open"
End Sub

The range begins at A1, so it contains the header and ends at the last row found in column A. The last column is found from row 1, which assumes the headers are contiguous enough for the rightmost header to mark the end of the fields. If your filter should cover only columns A through F, you can use F as the fixed right edge instead of calculating lastCol. A fixed column boundary can be clearer when unrelated headings appear farther across the worksheet.

The guard checks that there is at least one record below the header and that the range has at least three columns. It prevents the example from applying a third-field filter to a range that is too small. Change the worksheet name, anchor column, field number, and criterion to match your workbook.

Understand Field and header numbering

In Range.AutoFilter, the Field argument is a one-based position within the filtered range, not necessarily the worksheet’s column number. If the range begins in column A, Field 3 means column C. If the range begins in column D, Field 3 means column F. This relative numbering is a frequent source of filters being applied to the wrong heading.

The first row of the filter range is treated as the header row. Do not start the range on the first record and expect VBA to identify the headings above it; include the header row explicitly. Similarly, if the headings are on row 4, build the range from row 4 rather than row 1, and calculate the last data row using an appropriate column.

For a different criterion, change Criteria1. For example, a text filter for a value such as Closed would use that text as the criterion. Number, date, wildcard, and multiple-criteria filters have their own syntax and should be chosen to match the data type and intended logic. The last-row calculation itself is independent of the criterion.

When a single anchor column is not enough

If valid records sometimes have a blank in the chosen anchor column, a whole-sheet search can locate the last row containing a constant or formula. This is useful when several columns jointly make up the list, but it can include unrelated material elsewhere on the worksheet. Limit the search to a known data area if possible, or use a table to define the boundary explicitly.

The following snippet finds the last row containing a value or formula anywhere on the worksheet. It does not apply a filter by itself; use the resulting lastRow when constructing the range. Save a copy before running a macro, because Ctrl+Z cannot reverse VBA actions.

Sub FindLastOccupiedRow()
    Dim ws As Worksheet
    Dim lastCell As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Search cell contents, including formulas, rather than formatting.
    Set lastCell = ws.Cells.Find(What:="*", _
                                After:=ws.Cells(1, 1), _
                                LookIn:=xlFormulas, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False)

    If lastCell Is Nothing Then Exit Sub
    lastRow = lastCell.Row
End Sub

Specifying the Find options is important because Excel can remember settings from a previous Find operation. Searching for formulas also finds formulas whose displayed result is an empty string. If those formulas extend well below the visible records, this method may return a later row than you want. Likewise, a note or other content below the list can become the last cell. “Last occupied cell” and “last record” are not always the same thing.

Use an Excel Table for growing data

When a dataset is a good fit for an Excel Table, a table often removes the need to calculate the final row manually. Tables expand when users type or paste records immediately below them, and their filter buttons are associated with the table’s own columns. In Excel for Microsoft 365 on Windows, create one by selecting the data and choosing Insert > Table, then confirm that the range has headers.

VBA can refer to a table by its name and apply a filter to its range. This example assumes a table named Table1 with at least three columns. Save a copy before running a macro.

Sub FilterTable()
    Dim ws As Worksheet
    Dim tbl As ListObject

    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("Table1")

    ' Stop if the table has no data rows.
    If tbl.DataBodyRange Is Nothing Then Exit Sub

    ' Field 3 is the third table column, not worksheet column C by definition.
    tbl.Range.AutoFilter Field:=3, Criteria1:="Open"
End Sub

Tables are especially useful when the data grows regularly or when the worksheet contains notes and other content outside the list. They are not mandatory: a normal range works well when its boundaries are predictable. If you use a table, make sure the macro targets the correct table name and that the selected field position matches the table’s column order.

Common problems and how to avoid them

  • The final records are not filtered: The anchor column may be blank on the last records, or the range may use a hard-coded row number. Choose a required field or define the list as a table.
  • Blank rows appear inside the list: A blank row in the middle does not necessarily stop the column-based End(xlUp) calculation, but it can make the displayed data look fragmented. Check that the range represents one contiguous dataset and that the header row is correct.
  • The wrong column is filtered: Recount Field from the first column of the range. Field 1 means the first field in that range, not always worksheet column A.
  • The macro reports a subscript error: Verify the worksheet name and, for the table example, the table name. Those names must match the workbook.
  • Old filter arrows or criteria remain: A worksheet may already have an AutoFilter or a filtered table. Decide whether the macro should change that existing filter or replace it. Do not clear filters indiscriminately if users rely on other filters on the same sheet.

When diagnosing a result, inspect the calculated row number and the range address in the VBA editor or with a temporary message box. That helps distinguish a wrong boundary from a wrong field index or criterion. Remove diagnostic code once the macro works as intended.

Run and maintain the macro safely

On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the procedure there. On a Mac, use Option+F11 (on some keyboards, Fn+Option+F11) or choose Developer > Visual Basic. The exact keyboard behavior can depend on the Mac keyboard’s function-key settings. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm); a standard .xlsx file does not preserve VBA code.

Before running a macro, save a backup copy and check that its sheet, table, header row, and anchor column match the workbook. Macros can alter workbook state and cannot be reversed with Ctrl+Z. In Excel for the web, you can open and edit many macro-enabled workbooks, but you cannot create, run, or edit VBA macros there; use the desktop Excel application for this VBA procedure.

Frequently Asked Questions

How do I get the last row in Excel VBA?
For a column that marks every record, use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. Replace A with the appropriate anchor column. The method works upward from the bottom of the worksheet to the last occupied cell in that column.

Does AutoFilter Field count from column A?
No. Field counts from the left edge of the range passed to AutoFilter. If the range begins in column C, Field 1 means column C, and Field 3 means column E.

Can I use End(xlUp) if there are blank cells?
Yes, if the chosen anchor column still has a value in the final record. Blank cells in the middle do not automatically make the final row calculation stop, but a blank at the bottom of that column can make the result too small.

What is the safest option when new rows are added often?
An Excel Table is usually a practical choice because it expands with added records and carries its filter with the data. If you use an ordinary range, calculate the boundary from a reliable required column each time the macro runs.

Why does my macro filter the wrong field?
Check the left edge of the range and count the columns from there. The Field number is relative to that range, so changing the range’s starting column can change which worksheet column Field 3 refers to.

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.