Know rowindex after filtering

[Maileen]

Hi,

Via VBA i filtered a sheet. Basically i should have as result only 1 row.

How can i know what is the rowindex (figure in the grey column margin
e.g : 873, or 34245,...) ?

thanks a lot,
Maileen

 

[Dave Peterson]

You have a reply to your post in .excel.

Hi,

Via VBA i filtered a sheet. Basically i should have as result only 1 row.

How can i know what is the rowindex (figure in the grey column margin
e.g : 873, or 34245,...) ?

thanks a lot,
Maileen

 

[Tom Ogilvy]

Assuming an autofilter
Dim rng as Range, rng1 as Range
set rng = Activesheet.autoFilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count-1,1)
On error resume next
set rng1 = rng.SpecialCells(xlvisible)
On Error goto 0
if not rng1 is nothing then
msgbox rng1(1).row
if rng1.count > 1 then msgbox "Multiple matches"
else
msgbox "No matches"
End if

Dim rng as Range, res as Variant
set rng = Activesheet.autoFilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count-1,1)
res = Application.Match(filtervalue,rng,0)
if not iserror(res) then
msgbox rng(res).Row
else
msgbox "No Matches"
End if