How to get the starting and ending rownum when using EXCEL Autofilter function in VBA

Question

How to get the starting and ending rownum when using excel Autofilter function in VBA.

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

so on using the autofilter i might end with startrow as 74562 and ending 87000.

Can someone show a macro which could output the startrow and endrow for every filter I use.

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 StartRow variable and 77343 in EndRow variable. How to do that?

Answer 1

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 $A$73351:$A$77343

StartRow = r.Row ' returns 73351
EndRow = r.Row + r.Rows.Count - 1 ' returns 77343

Answer 2

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

Answer 3

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