Select true last cell of filtered data

Question

I'm trying to select the true last cell data set then insert a subtotal into the cell below. Everything I've tried selects the last cell of my visible data. I need the last cell of the entire sheet.

For instance, my filtered data will have rows 3:10 visible. My code is selecting row 10 when I need it to select the last row of the entire dataset that's hidden (row 100).

This will be performed on multiple sheets at once.

Here's my code:

Sub Example()

Dim LastRow As Long

With ActiveSheet
    LastRow = .Range("J2").SpecialCells(xlCellTypeLastCell).Row

End With

Call SubTotalMacro

End Sub

Thanks!

https://stackoverflow.com/questions/12586848/get-last-row-from-filtered-range — BigBen, Oct 14 '20 at 22:15
Wow, not sure how that didn't cross my mind. Thanks for your help. — cpaneedhelp, Oct 15 '20 at 13:20

score 0 · Answer 1 · answered Oct 14 '20 at 22:26

0

Try this: MsgBox ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

answered Oct 14 '20 at 22:26

Muzaffer Galata

580
1
9
22

1

A note: `UsedRange` is not reliable. It may extend past where the user expects it to stop. More discussion [here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Oct 14 '20 at 22:29
Is it also risky in @cpaneedhelp's case? – Muzaffer Galata Oct 15 '20 at 14:58
It could be, yes. – BigBen Oct 15 '20 at 14:59

Select true last cell of filtered data

1 Answers1