VBA Find the Last Used Cell When Filters Are On

To find the last used cell in Excel VBA while a filter is active, search the worksheet with Range.Find; AutoFilter hides rows but does not remove their contents. Use SearchOrder:=xlByRows to locate the last populated row, and check the returned cell’s row if you need the last visible result instead.

Why filters do not change the search

An Excel filter changes which rows are displayed. It does not delete the rows that fail the filter, so a search across the worksheet can still find values and formulas in those hidden rows. That behavior is usually what you want when finding the actual last used cell in the data, but it can surprise you if you expect the answer to be the last visible record.

For example, suppose rows 2 through 500 contain records and a filter leaves only rows 12, 35, and 81 visible. If row 500 contains data but is filtered out, a whole-sheet search can return a cell in row 500. That is correct for the last used cell on the worksheet; it is not the last cell in the filtered view.

It helps to decide what “last” means before writing the macro:

Last used cell: the furthest cell containing a constant or formula, whether its row is visible or filtered out.
Last used row: the greatest row number containing content, which may have other content in columns farther to the right.
Last visible data cell: the furthest qualifying cell whose row and column are currently visible.

Use Find to get the last used cell

This macro searches the active worksheet from the end of the sheet backwards. With SearchOrder:=xlByRows, it finds the last populated row and returns the last matching cell in that row. It includes formulas even when a formula currently displays an empty string, because the search looks in formulas.

Sub ShowLastUsedCell()
    Dim ws As Worksheet
    Dim lastCell As Range

    ' Search the active worksheet, including filtered-out rows.
    Set ws = ActiveSheet
    Set lastCell = ws.Cells.Find(What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False, _
        SearchFormat:=False)

    If lastCell Is Nothing Then
        MsgBox "No constants or formulas were found."
    Else
        MsgBox "Last used cell: " & lastCell.Address(False, False) & _
            " (row " & lastCell.Row & ")"
    End If
End Sub

Save a copy of the workbook before running a macro. This example only searches and displays a message, but VBA macros can change workbook contents, and their changes cannot be undone with Ctrl+Z. On Windows, open the VBA editor with Alt+F11, insert a standard module through Insert > Module, paste the code, and run ShowLastUsedCell. In Excel for Mac, the VBA editor is available in the desktop app; the shortcut is Option+F11 or Fn+Option+F11 depending on the keyboard. Excel for the web does not run VBA macros.

What the Find settings mean

Passing the search options explicitly matters because Excel remembers several Find settings from earlier searches in the user interface or VBA. If a macro leaves settings implicit, a prior search can affect its result. The code specifies the important options rather than relying on whatever was last selected in Find.

What:="*" searches for content rather than a specific word.
LookIn:=xlFormulas checks formulas and constants. A formula returning "" still counts because a formula exists in the cell.
LookAt:=xlPart allows a match within the cell content.
SearchOrder:=xlByRows prioritizes the last row. Use xlByColumns when your main question is the last used column.
SearchDirection:=xlPrevious searches backwards. Starting after A1 lets the search wrap around and reach the end of the sheet.
SearchFormat:=False prevents a previously selected Find format from restricting the search.

The address returned is one cell, not a range boundary or a guarantee that every cell above and to its left is part of a neat table. A worksheet may contain separate blocks of data, notes, or stray content. The result simply reflects the search criteria across the whole worksheet.

Find the last visible cell instead

If the requirement is specifically “last cell among the rows I can currently see,” search backwards and skip any result whose row or column is hidden. The function below treats manually hidden rows and columns as hidden too, not only rows hidden by AutoFilter. It returns Nothing if no visible cell with a formula or constant is found.

Function LastVisibleDataCell(ByVal ws As Worksheet) As Range
    Dim hit As Range
    Dim firstAddress As String

    ' Start with the last match on the sheet.
    Set hit = ws.Cells.Find(What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False, _
        SearchFormat:=False)

    If hit Is Nothing Then Exit Function
    firstAddress = hit.Address

    Do
        ' Ignore matches in hidden rows or hidden columns.
        If Not hit.EntireRow.Hidden And Not hit.EntireColumn.Hidden Then
            Set LastVisibleDataCell = hit
            Exit Function
        End If

        Set hit = ws.Cells.Find(What:="*", _
            After:=hit, _
            LookIn:=xlFormulas, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False, _
            SearchFormat:=False)

        If hit Is Nothing Then Exit Do
        If hit.Address = firstAddress Then Exit Do
    Loop
End Function

Call the function from another procedure by assigning its result to a Range variable and checking whether that variable is Nothing before using it. For instance, a cell that is visible but blank is not a match; a visible formula that evaluates to an empty string is a match under xlFormulas. If you want visible displayed values only, consider whether that formula behavior matches your definition and choose the search approach accordingly.

Last used cell versus last row or table row

These terms are often used as if they mean the same thing, but they can produce different answers. The sample’s row-based search returns a last matching cell in the final populated row. If you only need the row number, use lastCell.Row. If your table occupies columns A through F and column X contains an unrelated note, a whole-sheet search can be influenced by that note even though it is outside the table.

For table-specific work, limit the search to the table’s data range instead of searching every cell on the sheet. Excel tables expose a ListObject and its DataBodyRange; that range excludes the header row. A table with no data rows can have no usable body range, so check for that condition before searching it. Filtering a table still hides records rather than erasing them, so a search confined to the table can still return a filtered-out record unless you also apply a visibility check.

Why UsedRange and Ctrl+End can mislead

UsedRange is convenient when you need the worksheet’s recorded used area, but it is not always the same as the final cell containing meaningful content. Formatting applied far below or to the right of the data can make the used area larger than expected. Similar surprises can occur with Ctrl+End, which moves to Excel’s last cell in the used range, not necessarily the last cell containing a value.

That does not make UsedRange useless. It can be a suitable starting boundary for operations that intentionally include formatting, or when you need to examine a bounded region rather than a million-row worksheet. For a content-based last cell, Find with explicit options is generally clearer. If the used range has expanded because of accidental formatting, removing the unused formatting and saving the workbook may help Excel reset it, but inspect the affected area first so that needed formatting is not lost.

Common problems and practical checks

The answer is on a filtered-out row. That is expected for a whole-sheet search. Use a visibility-aware method only if the visible subset is the intended scope.
A formula that looks blank is returned. With xlFormulas, a formula is content even when its displayed result is an empty string. Decide whether formulas or displayed results define “used” for your task.
A formatted blank cell is not returned. The wildcard search looks for content, not formatting alone. Use a different method if formatting boundaries are what you need.
The search seems to stop in the wrong column. Row-based order answers a last-row-oriented question. Column-based order answers a last-column-oriented question; neither automatically defines the edge of a specific table.
The result includes a note outside the data. Search the intended table or range rather than ws.Cells across the whole sheet.

Before relying on the result, compare the returned address with the data layout and check whether hidden rows contain records. If your macro will delete, copy, or resize a range based on the answer, test it on a workbook copy and make the target range explicit. A mistaken last-row assumption can affect unrelated content below or beside a filtered list.

Choosing the right approach

Use the whole-sheet Find pattern when the goal is to locate content regardless of filters, including formulas. Use the visible-only function when the current displayed subset is specifically the scope. Use a table’s data range when unrelated worksheet content must not affect the answer. Use UsedRange when the recorded used area, including formatting considerations, is relevant rather than strictly nonempty cells.

Filters can change the visible answer from one run to another, so a visible-only macro depends on the current filter selections and hidden-row state. A whole-sheet search does not depend on those selections. Keeping that distinction explicit makes the macro easier to maintain and prevents a filtered view from being mistaken for the underlying dataset.

Frequently Asked Questions

Does VBA Find include rows hidden by an Excel filter?
Yes. A search over the worksheet can find cell contents in rows hidden by AutoFilter. Filtering hides records from view; it does not remove them from the worksheet.

How do I find the last visible row after filtering?
Search backward through matches and ignore cells whose entire row is hidden, as in the visible-cell function above. It also skips manually hidden rows, so that behavior is broader than checking AutoFilter alone.

Does Find count a formula that returns an empty string?
With LookIn:=xlFormulas, yes: the formula itself is present in the cell. That is useful when formulas define the used area, but it differs from treating only nonblank displayed results as data.

Why does Ctrl+End go farther than my data?
Ctrl+End moves to the last cell in Excel’s used range, which can extend beyond the actual data because of formatting or prior edits. A content-based Find search can give a different, often more relevant, result.

Can I run this macro in Excel for the web?
No. Excel for the web does not execute VBA macros. Run VBA in the desktop version of Excel for Windows or Mac; the worksheet’s filter itself can still be used in the web app.

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.