vba 在VBA中使用EXCEL Autofilter函数时如何获取开始和结束的rownum
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7171814/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the starting and ending rownum when using EXCEL Autofilter function in VBA
提问by Sunny D'Souza
How to get the starting and ending rownum when using excel Autofilter function in VBA.
在VBA中使用excel Autofilter函数时如何获取开始和结束的rownum。
I have a set of 500k+ records. and use filters on a particular column. Due to this because of filters the starting rownum might change since other records are hidden in autofilter
我有一组 500k+ 记录。并在特定列上使用过滤器。由于这个原因，由于其他记录隐藏在自动过滤器中，因此起始 rownum 可能会更改
so on using the autofilter i might end with startrow as 74562 and ending 87000.
所以使用自动过滤器时，我可能会以 startrow 为 74562 并以 87000 结尾。
Can someone show a macro which could output the startrow and endrow for every filter I use.
有人可以展示一个宏，它可以为我使用的每个过滤器输出 startrow 和 endrow。
The data is sorted so any filter would be a fixed consecutive bunch
数据已排序，因此任何过滤器都将是固定的连续束
EDIT:I have realized that the following code gives the result but in range
编辑：我意识到以下代码给出了结果但在范围内
MsgBox ActiveSheet.Range("A2:A81000").Rows.SpecialCells(xlCellTypeVisible).Address
It shows result as $A$73351:$A$77343. But I just want the 73351 in StartRowvariable and 77343in EndRowvariable. How to do that?
结果显示为$A$73351:$A$77343。但我只想在73351StartRow变量，77343在EndRow变量。怎么做？
回答by Jean-Fran?ois Corbett
You ask how to return the row number of the first and last row in a range. This is how:
您询问如何返回范围中第一行和最后一行的行号。这是如何：
Dim r As Range
Dim StartRow As Long
Dim EndRow As Long
Set r = ActiveSheet.Range("A2:A81000").Rows.SpecialCells(xlCellTypeVisible)
' r is now $A351:$A343
StartRow = r.Row ' returns 73351
EndRow = r.Row + r.Rows.Count - 1 ' returns 77343
回答by Vinny Roe
dim rRange as Range
Set rRange = Range("A1")
Debug.Print rRange.Offset(1, 0).Row
Debug.Print rRange.Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row
回答by JMax
You can try this piece of code:
你可以试试这段代码：
Sub Get_Filtered_Range()
Dim oWS As Worksheet
Dim oRng As Range
Dim oColRng As Range
Dim oInRng As Range
oWS = ActiveSheet
'Trigger any autofilter if needed
'oWS.UsedRange.AutoFilter(Field:=2, Criteria1:="Test")
oRng = oWS.Cells.SpecialCells(xlCellTypeVisible)
oColRng = oWS.Range("A2:A"&ActiveSheet.Rows.Count)
oInRng = Intersect(oRng, oColRng)
'display the values in the VBEditor
Debug.Print("Filtered Range is " & oInRng.Address)
Debug.Print("First Row Filtered Range is " & oInRng.Rows(1).Row)
End Sub
Adapted from here
改编自这里
