Finding Last Used Row in filtered data

Question

Code to find the last used row in filtered data.

Sub find()

Dim rFiltered As Range

With ActiveSheet.AutoFilter.Range
    Set rFiltered = .Resize(.Rows.Count - 1).Offset(1).Columns(2).SpecialCells(xlCellTypeVisible)
End With

'is shows 419 - and that is fine
FirstRow = rFiltered.Cells(1, 1).End(xlToLeft).Row
'it shows the very last row 1048576, not the last row in a filtered range
'it should be 423
LastRow = rFiltered.Cells(FirstRow).End(xlDown).Row

For r = FirstRow To LastRow
    If Sheet1.Cells(r, 2).Value = "Maria L" Then
        Rows(r).Interior.Color = rgbBlue
    End If
Next r
    
End Sub

Sample data that is filtered

R Number    Name
4879668 Maria L
4879668 Thomas Y
4879668 Eleanor B
4879668 Eleanor B
4879668 Sandra J

The first used row here is 419, the last one is 423, but it shows the very last row 1048576.

The code shows correct LastRow only when filtered data begins from second row.

That is one of the problem using `xlDown`. You may want to see [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) — Siddharth Rout, Mar 05 '20 at 14:29
The only way I can replicate your issue, using your code, is if I filter the list via a column other than the 2nd, so that the 2nd column is completely blank. — CLR, Mar 05 '20 at 14:41

Gary's Student · Answer 1 · 2020-03-08T20:24:43.537

0

Say we start with:

and then apply a filter:

The following will get the row number of the last visible row in the filtered data:

Sub framm()
    Dim r1 As Range, r2 As Range, r3 As Range, Last As Long

    Set r1 = ActiveSheet.AutoFilter.Range
    Set r2 = r1.SpecialCells(xlCellTypeVisible)
    Set r3 = r2.SpecialCells(xlCellTypeLastCell)
    Last = r3.Row

    MsgBox Last
End Sub

edited Mar 08 '20 at 20:24

answered Mar 05 '20 at 14:56

Gary's Student

95,722
10
59
99

score 0 · Answer 2 · edited Jul 11 '20 at 14:07

0

I changed this part:

LastRow = rFiltered.Cells(FirstRow).End(xlDown).Row

to this:

LastRow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row

edited Jul 11 '20 at 14:07

Community

1
1

answered Mar 05 '20 at 15:30

MrNoNo

65
1
10

score 0 · Answer 3 · answered Mar 05 '20 at 15:57

To explain why add some debug.print statement.

With ActiveSheet.AutoFilter.Range
    Set rFiltered = .Resize(.Rows.Count - 1).Offset(1).Columns(2).SpecialCells(xlCellTypeVisible)
End With
Debug.Print rFiltered.Address

firstrow = rFiltered.Cells(1, 1).End(xlToLeft).Row
Debug.Print firstrow
Debug.Print rFiltered.Cells(firstrow).Address

Applying the filter for Eleanor B and single step with F8

1st print shows $B$4:$B$5 for the filtered address

2nd print shows 4 for $B$4 row

3rd print shows $B$7 ? Why, because it's the 4th row of filter range "$B$4:$B$5"

$B$7 is empty so the last row is the bottom.

score 0 · Answer 4 · answered Jul 11 '20 at 18:35

This is a tricky proposition at best. Do you have headers? Do you have totals? So I came up with this code to at least cope with the totals (actually subtotals):

Sub GetLastRowFiltered()

    Dim rng1   As Range
    Dim rng2   As Range
    Dim lFirst As Long
    Dim lLast  As Long
    Dim lCol   As Long

    Set rng1 = ActiveSheet.AutoFilter.Range
    Set rng2 = rng1.SpecialCells(xlCellTypeVisible)

    lFirst = rng2.Row()
    lCol = rng2.Column()
    lLast = Cells(Application.Rows.Count, lCol).End(xlUp).Row()

    MsgBox "First Row: " & Format(lFirst, "###") & vbCrLf & _
           "Last  Row: " & Format(lLast, "###"), _
           vbInformation + vbOKOnly, _
           "Filtered Range Info:"

End Sub 'GetLastRowFiltered

As shown above you'll get this answer:

If you add 1 to: lCol = rng2.Column() + 1

You'll get this:

Haven't even started thinking about getting the first real data row.

BTW: Gary's solution kept giving me 233 as the last row, and the last row in my list is 186, 187 with totals. I have no idea why.

HTH

Finding Last Used Row in filtered data

4 Answers4