VBA从excel中的筛选表返回第n行数 [英] VBA to return nth row number from a filtered table in excel
问题描述
任何人都可以帮助我找到在Excel表中应用过滤器后的第n个元素的绝对行数。
Can anyone please help me with finding the absolute row number of nth element after filter is applied in an excel table.
例如,我有过滤器,并有一个可见范围的数据元素。当没有过滤器打开时,此过滤范围的第20行(第n行)可以是第60行(绝对值)。有没有办法使用VBA找到绝对行号?
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?
推荐答案
最简单的方法是特殊单元格。见下文:
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...
现在,当我们运行上面发布的脚本时,我们的消息框将显示每个未过滤行的绝对行号。结果是1,3,4,5和7。
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.
这篇关于VBA从excel中的筛选表返回第n行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!