Geek Answers Handbook

VBA to return the nth row number from a filtered table in excel

Can someone help me find the absolute row number of the nth element after applying the filter in the excel table.

For example, I have a filter and there is a visible range of data items. Now the 20th (nth) row in this filtered range can be the 60th row (absolute meaning) if the filters are not enabled. Is there a way to find the absolute line number using VBA?

+4
source share
3 answers

The 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 that this method will not work, but I am sure that it does what you ask. Example:

- , , . Unfiltered

- ...

Filered

, script, , "" . - 1,3,4,5 7.

+4

,

Function RowNum(Target As Range) As Long
    RowNum = Target.Row
End Function

, =RowNum(E9). , 21, ( , )... . =rownum(A2)-rownum($A$1)... !

, SelectionChange ( - ).

VBA CELL... . =CELL("row",A1)

0

, @Lopsided wont work, n- . .

, n script. . - , . (

Sub absoluteRowID()

Dim RowCount, hiddenRows As Integer

'relative position n
n = 5
i = 0

Do While i < n

i = i + 1
If ThisWorkbook.Sheets(1).Rows(i).EntireRow.Hidden Then
'if there is a hidden row, position is incremented
n = n + 1
End If
'if there is no hidden row, nothing happens

Loop

MsgBox (i)



End Sub

-1

More articles:


All Articles