VBA to return nth row number from a filtered table in excel [closed]

Question

Can anyone please help me with finding the absolute row number of nth element after filter is applied in an excel table.

For example, I have filter on and have a visible range of data element. Now 20th (nth) row in this filtered range could be 60th row (absolute sense) when no filters are on. Is there a way to find the absolute row number using VBA?

Answer 1

Simplest method is special cells. See below:

Sub test()

Dim i As Integer, j As Integer, k As Integer
Dim Report As Worksheet

Set Report = Excel.ActiveSheet ' Store the current worksheet in a variable (always a good idea)
Dim visRng As Range ' Creating a range variable to store our table, excluding any rows that are filtered out.
Set visRng = Report.UsedRange.SpecialCells(xlCellTypeVisible) ' Select only rows within the used range that are visible.

Dim r As Range 
For Each r In visRng.Rows ' Loop through each row in our visible range ...
    MsgBox (r.Row) ' ... and retrieve the "absolute" row number.
Next

End Sub

---

EDIT

Tom claims this method will not work, but I'm pretty sure it does what you ask. Example:

Here is my original test table--unfiltered so you can see what we're doing.

And then we filter a couple values somewhere in the middle of the table...

Now when we run the script I posted above, our message box will show the "absolute" row number for each unfiltered row. Results are 1,3,4,5, and 7.