How to find first row after filter

[vinmam123VBA]

Hi,

Can you please help me in finding Firstrow after applying filter?

Would like to find the row number highlighted after applying filter

Thanks,
Vin

 

[Fluff]

How about

   MsgBox Range("A2:A" & Rows.count).SpecialCells(xlVisible)(1).Row

 

[vinmam123VBA]

How about

   MsgBox Range("A2:A" & Rows.count).SpecialCells(xlVisible)(1).Row

Thank for the response! It is not selecting dynamically. For example if filter changed then visible row number will also get change that time frow number should also get change

Regards,
Vinayak

 

[Fluff]

There is nothing in VBA that detects a change in the autofilter.
If you have any formulae on the sheet, you could use the calculation event, but that will trigger whenever any cell on the sheet recalculates.
Another possible option would be to use a selection change event, but that is not guaranteed to work, as you can change the filter without selecting a different cell.

 

[Joe4]

How about

MsgBox Range("A2:A" & Rows.count).SpecialCells(xlVisible)(1).Row

I actually had to deal with something like this yesterday. The issue that I was having was with the wrong value being returned when the filter returned NO data (it would return the first visible blank row instead). So here is a little edit to your code that handles that:

    Dim r As Long
    r = Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1).Row
    
    If Cells(r, "A") <> "" Then
        MsgBox "First row in filter is " & r
    Else
        MsgBox "No data returned by filter"
    End If