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. UseOn Error Resume Nextand check if the range isNothing. - Blank rows within data: If your data has blank rows,
UsedRangeorEnd(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
SpecialCellsreturnsNothing. - 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
- Excel Template for Project Expense Tracking
- Excel Template for Snowball Debt Payoff
- Excel Template for Paying Off Debt: Free & DIY
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Loop Through Cells in a RangeTo loop through every cell in an Excel range with VBA, use a For Each loop and a…
- Excel VBA Type Mismatch: Causes and FixesIn Excel VBA, “Run-time error 13: Type mismatch” means VBA tried to use a value as a different…
- Excel VBA: Stop an Endless Loop and Recover SafelyTo stop a running Excel VBA loop on Windows, press Ctrl+Break (on some keyboards, use Ctrl+Fn+Pause), then choose…
- VBA Dictionary SumIf: How to Group and Sum Data in ExcelTo perform a SUMIF-style total in VBA, use a Scripting.Dictionary: make each category a key and add each…
- Call a Macro from an Excel UserForm ButtonYes. In Excel for Microsoft 365 on Windows, put a call to a Public Sub in the UserForm…
- Excel VBA: Loop Through JSON Object Arrays Step by StepTo loop through a JSON array of objects in Excel VBA, parse the JSON with a VBA-compatible parser…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.