To apply VBA AutoFilter to a range that changes size, calculate the range’s last row (and, if needed, last column) before calling AutoFilter. For a consistent data block with headers in row 1 and a reliable key column in A, build the range from A1 to the last populated cell in column A, then filter it using a field number counted from the range’s left edge.
Why make the AutoFilter range dynamic?
A fixed reference such as A1:D200 stops matching the data when new records are added or old ones are removed. If the range is too short, new rows may not receive filter arrows or be included in the results. If it is unnecessarily large, the filter can include blank rows, unrelated information, or formatting far below the data.
A dynamic range is recalculated when the macro runs, so it can reflect the current size of the list. This is useful for recurring imports, reports refreshed from another system, and workbooks where users append records between runs. The key decision is how to identify the data’s real boundaries: there is no single last-row technique that is correct for every worksheet layout.
How AutoFilter uses a range
AutoFilter applies to a rectangular range with a header row. Each header represents a field, and VBA’s Field argument identifies a column by its position within that range—not necessarily by its worksheet column letter. If the filtered range starts in column A, Field:=3 means column C. If it starts in column B, Field:=3 means column D.
The range should include the headers and the records to filter. For example, a block from A1:D50 has four fields and includes row 1 as its header row. Headers should be present and distinct enough for people to understand the dropdowns. A completely blank row inside the data can also make a current-region approach stop early, so check the structure before choosing the range method.
Choose a last-row method that fits the data
For a well-structured list, using a reliable key column is often the clearest option. A key column contains a value for every record, such as an order ID. The VBA expression Cells(Rows.Count, "A").End(xlUp).Row starts at the bottom of column A and moves up to its last non-empty cell. It is simple and fast, but it will miss records below a blank in column A if those records have values elsewhere.
Other approaches suit different layouts:
- Fixed known columns and a key column: Find the last row from the key column and set the final column explicitly. This is predictable when the report always has the same fields.
- Current region:
Range("A1").CurrentRegionreturns the connected block around A1. It is convenient for a compact list, but blank rows or columns can divide the data into separate regions. - Find the last used cell: Search the worksheet for the last cell containing a value or formula, then use its row and column. This handles changing columns but may include notes or other content outside the intended table.
- Excel Table: Use a ListObject when the data is naturally a structured list. Tables expand as records are added and are usually the most maintainable option.
Example: filter a range with a changing row count
This macro assumes the worksheet is named Data, headers are in A1:D1, column A has a value on every record row, and the status to filter is in column C. It finds the last row using column A and fixes the range width at four columns. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.
Option Explicit
Sub FilterOpenRecords()
Dim ws As Worksheet
Dim lastRow As Long
Dim dataRange As Range
' This example expects a dedicated data sheet named Data.
Set ws = ThisWorkbook.Worksheets("Data")
' Column A must be populated for every record.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Keep a header-only sheet from being treated as a data list.
If lastRow < 2 Then
MsgBox "There are no data rows to filter."
Exit Sub
End If
' Remove the worksheet's existing filter arrows before applying this range.
If ws.AutoFilterMode Then ws.AutoFilterMode = False
' A1:D(lastRow) includes the header and all current records.
Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, 4))
dataRange.AutoFilter Field:=3, Criteria1:="Open"
End Sub
Because the range starts in column A, Field:=3 filters column C. If the range began in B instead, the same field number would refer to D. The macro deliberately turns off any existing worksheet AutoFilter arrows first; on a sheet with another filter that must be preserved, do not use that cleanup step without adapting the macro.
The row calculation uses the last non-empty cell in column A, not the last visibly formatted row. If column A contains formulas that display an empty string, those formula cells can still affect where the search ends. Choose a key column whose contents reliably indicate the records you intend to include.
Finding the last used row and column
When both the row count and the number of fields vary, a Find-based search can locate the final cell by row and by column. This example starts at A1 and assumes the intended list occupies the worksheet’s used content. Find can also pick up a note or a separate block elsewhere on the sheet, so a dedicated data sheet or a known starting and ending column is safer when unrelated content exists.
Option Explicit
Sub FilterUsedBlock()
Dim ws As Worksheet
Dim lastRowCell As Range
Dim lastColCell As Range
Dim dataRange As Range
Dim lastRow As Long
Dim lastCol As Long
' The sheet should contain only the data block being filtered.
Set ws = ThisWorkbook.Worksheets("Data")
' Search formulas and values, ignoring formatting-only cells.
Set lastRowCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
If lastRowCell Is Nothing Then Exit Sub
Set lastColCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
lastRow = lastRowCell.Row
lastCol = lastColCell.Column
If lastRow < 2 Or lastCol < 1 Then Exit Sub
If ws.AutoFilterMode Then ws.AutoFilterMode = False
Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
dataRange.AutoFilter Field:=3, Criteria1:="Open"
End Sub
Setting the Find options explicitly matters because Excel remembers several Find settings from previous searches and dialogs. The code treats row 1 as the header row and expects at least three columns because it filters field 3. If the list does not start at A1, adjust the starting cell and the range’s first row and column; the field number remains relative to the resulting range.
Using an Excel Table instead
For a list that regularly grows, converting it to an Excel Table is often easier than calculating its boundaries each time. Select a cell in the list and choose Insert > Table, confirm the range, and make sure the table has headers. You can also use Home > Format as Table. Give the table a useful name through Table Design > Table Name, then refer to that table in VBA.
For a table named Orders on the Data sheet, a macro can use Set lo = ws.ListObjects("Orders") and apply the filter to lo.Range. The table range adjusts as rows are added through the table, and its column names make formulas and VBA easier to interpret. In a table, AutoFilter’s field number is still positional within the table: field 3 means its third column, not necessarily worksheet column C.
A Table is not a cure for every layout problem. Keep unrelated notes outside the table, and ensure new records are added as table rows so they become part of its range. If the list is imported as a plain block or has unusual gaps, a deliberate VBA range calculation may be more suitable.
Text, multiple values, and other criteria
The example uses an exact text criterion, Criteria1:="Open". To filter a numeric field, use a comparison such as Criteria1:=">100"; the criterion is passed as text even though Excel evaluates it as a number condition. For two alternative conditions on one field, AutoFilter supports an operator and a second criterion, for example Criteria1:="Open", Operator:=xlOr, Criteria2:="Pending".
To show a set of specific text values, the following pattern uses a value array:
dataRange.AutoFilter Field:=3, _
Criteria1:=Array("Open", "Pending"), Operator:=xlFilterValues
These criteria filter the records; they do not change the underlying cell values. If the macro is run repeatedly with different criteria, decide whether the previous filter should be replaced, cleared, or deliberately combined with other field filters. A filter can be applied to one field while existing filters on other fields remain active, depending on how the range and sheet’s filter state are managed.
Common problems and how to avoid them
- New records are missing: The calculated last row may rely on a column that is blank in those records. Use a dependable key column or a Table.
- The wrong column is filtered: Recount the field from the range’s left edge. Worksheet column C is field 3 only when the range starts in A.
- Only headers show: Check that the last-row calculation is greater than the header row and that the range actually includes the records.
- Unrelated rows appear in the filter: The last-used-cell search may have found notes, formulas, or another block. Restrict the range to the dataset’s known columns or move unrelated content elsewhere.
- The macro reports an AutoFilter error: Confirm that the range includes a header row and that the field number exists within it. Also check whether a different filtered range is already active on the worksheet.
- Blank rows split the list: A CurrentRegion-based range can stop at blank rows or columns. Remove accidental gaps, use a key-column calculation, or convert the list to a Table.
Do not use UsedRange as a synonym for the data range without checking the sheet. Formatting applied far below the records can enlarge Excel’s used area, and other content can be included even when it is not part of the list.
Excel platform notes
These examples are for desktop Excel for Microsoft 365 on Windows. VBA macros cannot run in Excel for the web; open the workbook in desktop Excel to use them. Excel for Mac supports VBA and AutoFilter, although the ribbon layout and some editor shortcuts can differ. The range logic and AutoFilter field numbering remain the important concepts across desktop versions.
When macros are blocked, check the workbook’s file type and macro security settings rather than changing the range code first. Save macro-enabled workbooks as .xlsm if they need to retain VBA. Run unfamiliar macros only in a workbook you trust, and keep a backup because macro actions are not reversible through Excel’s normal Undo command.
Frequently Asked Questions
How do I find the last row in VBA for AutoFilter?
For a reliable key column A, use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. It finds the last non-empty cell in that column. If records can have gaps in A, choose another dependable column or search the intended data block more broadly.
Does the AutoFilter Field number mean the worksheet column?
No. It is counted from the first column in the range being filtered. In A1:D20, field 3 is worksheet column C; in B1:E20, field 3 is worksheet column D.
Can I filter a range that starts somewhere other than A1?
Yes. Build the range from the actual header cell to the last data cell, then count the field number from that range’s left edge. Ensure the first row of the range contains the headers.
Is CurrentRegion better than finding the last row?
It is convenient when the list is a single uninterrupted block. It can return a smaller region if blank rows or columns separate part of the data, so a key-column method or Table is often safer for lists with gaps.
Why can’t I undo a VBA AutoFilter macro?
Excel clears its regular Undo history when a macro runs. Save a copy before running VBA, especially if the macro also edits or deletes data. Filtering alone hides rows rather than deleting them, but the macro’s other actions may still be permanent.
Related
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
- Data Validation Studio: Excel Rules, Setup, and Limits
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Find a Cell with Specific Text in a RangeTo find a cell containing specific text with Excel VBA, use the Range.Find method. Set options such as…
- Excel VBA Enum: How It Works and When to Use ItAn Excel VBA Enum is a named set of numeric constants: it lets code use readable labels such…
- VBA Copy and Paste Special: Examples and OptionsIn Excel VBA, copy a range and paste only selected content with Range.Copy followed by Range.PasteSpecial. For example,…
- VBA Find: How to Handle a Failed SearchIn Excel VBA, handle a failed Range.Find search by checking whether its result is Nothing before using it.…
- Excel Macro Recorder: Keyboard Shortcuts and SetupYes. In Excel for Microsoft 365 on Windows, record a macro from Developer > Code > Record Macro…
- VBA Dictionary Length: How to Count ItemsIn VBA, use a Dictionary’s Count property to find how many key-value pairs it contains: for example, Debug.Print…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.