Excel VBA Last Visible Row: Find It Fast

VBA & macros · Updated November 22, 2026

The fastest way to get the last visible row in Excel VBA is to use ActiveSheet.UsedRange.Rows.SpecialCells(xlCellTypeVisible).Count combined with a loop, or more reliably, Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Areas when dealing with filtered data. For a filtered list, the cleanest method is: lastRow = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 (adjusting for header offset). This article explains why the simple End(xlUp) fails on filtered data, and gives you several robust alternatives.

Why the Obvious Answer Fails

Most Excel users know Range("A" & Rows.Count).End(xlUp).Row to find the last row with data. That works on an unfiltered sheet. But when a filter is active, End(xlUp) still returns the last row in the *entire dataset*, including hidden rows. That is because filtering only hides rows; it does not delete them. So if you have 100 rows of data and filter to show only 5, End(xlUp) returns 100, not the last visible row. This causes loops to process hidden rows, which often produces wrong results or runtime errors when copying visible cells only.

Understanding the SpecialCells Method

VBA’s SpecialCells method is the key to interacting with visible cells. The constant xlCellTypeVisible (value 12) returns only cells that are not hidden. However, SpecialCells(xlCellTypeVisible) on a range returns a Range object that may consist of multiple *areas* – contiguous blocks of visible cells separated by hidden rows. For example, if rows 3-5 are hidden, the visible cells in column A form two areas: rows 1-2 and rows 6-10. The .Areas collection lets you access each block. To get the last visible row, you need the last row of the last area.

Method 1: Using Areas on a Single Column

If your data is in a contiguous block (no blank rows within the data) and you have a header in row 1, this works:

Dim ws As Worksheet
Set ws = ActiveSheet
Dim visibleCells As Range
Set visibleCells = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
Dim lastVisibleRow As Long
lastVisibleRow = visibleCells.Areas(visibleCells.Areas.Count).Row + visibleCells.Areas(visibleCells.Areas.Count).Rows.Count - 1
MsgBox lastVisibleRow

This code assumes column A has data in every visible row. It finds the last area and calculates its bottom row. If there are no visible cells (e.g., filter returns nothing), SpecialCells raises an error – you must handle that with On Error Resume Next.

Method 2: Using AutoFilter.Range (Best for Filtered Lists)

When you have an AutoFilter applied, the filter’s range is stored in AutoFilter.Range. This includes the header row. To get the last visible data row (excluding header), use:

Dim af As AutoFilter
Set af = ActiveSheet.AutoFilter
If Not af Is Nothing Then
Dim lastVis As Long
lastVis = af.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
' Subtract 1 for the header row
MsgBox "Last visible data row: " & lastVis
Else
MsgBox "No filter applied"
End If

This counts the number of visible cells in the first column of the filter range, then subtracts one for the header. This works because SpecialCells(xlCellTypeVisible).Count returns the total number of visible cells across all areas. It assumes the first column has no blank cells within the data. If your first column has blanks, use a column that is always filled.

Method 3: Looping Through Rows (Slow but Simple)

For small datasets (under 10,000 rows), a simple loop from the bottom up is readable and reliable:

Dim r As Long
For r = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If Not Rows(r).Hidden Then
MsgBox "Last visible row: " & r
Exit For
End If
Next r

This checks the Hidden property of each row. It is slower than SpecialCells but avoids the multi-area complexity. It also works when rows are manually hidden (not just filtered). For manually hidden rows, SpecialCells(xlCellTypeVisible) still works, but the AutoFilter.Range method does not because there is no filter.

Handling Errors and Edge Cases

Three common pitfalls:

  • No visible cells: If a filter hides all data rows, SpecialCells(xlCellTypeVisible) throws error 1004. Use On Error Resume Next and check if the range is Nothing.
  • Blank rows within data: If your data has blank rows, UsedRange or End(xlUp) may give incorrect last row. Always base your range on the actual data extent, not the whole column.
  • Multiple areas: When using Areas, remember that the last area might not be the last visible row if there are hidden rows after it – but it is, because areas are contiguous blocks. The last area’s bottom row is the last visible row.

Here is a robust function you can paste into a module:

Function GetLastVisibleRow(ws As Worksheet, Optional col As Long = 1) As Long
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
If lastRow = 1 Then GetLastVisibleRow = 1: Exit Function
Dim vis As Range
On Error Resume Next
Set vis = ws.Range(ws.Cells(1, col), ws.Cells(lastRow, col)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If vis Is Nothing Then
GetLastVisibleRow = 0 ' No visible cells
Else
GetLastVisibleRow = vis.Areas(vis.Areas.Count).Row + vis.Areas(vis.Areas.Count).Rows.Count - 1
End If
End Function

Call it like GetLastVisibleRow(ActiveSheet). This function includes the header row if it is visible; adjust if needed.

Comparing Methods: Which to Use?

  • AutoFilter.Range – fastest and most accurate for filtered lists, but only works when a filter is active.
  • SpecialCells with Areas – works for both filtered and manually hidden rows, but requires handling multiple areas.
  • Loop – simplest to understand, good for small data, works with any hidden rows.
  • UsedRange – not recommended because it includes hidden rows and can be unreliable after deletions.

For most production code, the AutoFilter.Range method is the best choice when you know a filter is applied. If you need a general solution, use the SpecialCells function above.

Common Mistakes to Avoid

  • Using End(xlUp) on a filtered column – returns hidden rows.
  • Forgetting to subtract the header row when using Count.
  • Not handling the case where SpecialCells returns Nothing.
  • Using UsedRange.Rows.Count – this counts hidden rows and may include formatting-only cells.
  • Assuming the last visible row is the same as the last row of the last area – it is, but only if you correctly calculate the area’s bottom row.

Frequently Asked Questions

How do I get the last visible row in a filtered range without using AutoFilter?

Use SpecialCells(xlCellTypeVisible) on the data range and then take the last area’s bottom row. For example: Set rng = Range("A1:A100").SpecialCells(xlCellTypeVisible) then lastRow = rng.Areas(rng.Areas.Count).Row + rng.Areas(rng.Areas.Count).Rows.Count - 1.

Why does SpecialCells(xlCellTypeVisible).Rows.Count return the wrong number?

Because Rows.Count on a multi-area range returns the count of rows in the first area only, not the total. Use .Areas to iterate or use .Count on a single column to get the total visible cells.

Can I use End(xlUp) after filtering?

No. End(xlUp) ignores row visibility and returns the last row with data in the entire column, hidden or not. You must use SpecialCells or loop.

How do I copy only visible rows to another sheet?

Use Range("A1:D100").SpecialCells(xlCellTypeVisible).Copy then paste to a destination. This copies only visible cells. Remember to handle the case where no cells are visible.

Does this work on Excel for Mac?

Yes, VBA behaves the same on Mac, but the keyboard shortcut to open the VBA editor is Option+F11 instead of Alt+F11. The code itself is identical.

What if my data has blank rows in the middle?

Blank rows break the contiguous range assumption. You need to define your data range more precisely, perhaps using CurrentRegion or a named range. The AutoFilter.Range method still works because it uses the filter’s known range.

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.