Extract First Row of the Filtered Range using Excel VBA
We can create filters programmatically using Excel VBA () and also add multiple criteria to it (). Once we get the filtered data, either we extract the same or iterate each row in it and do some operations. Here is one such simple program to extract the rows of filtered range using VBA
Sub Get_Filtered_Range()
Dim oWS As Worksheet
Dim oRng As Range
Dim oColRng As Range
Dim oInRng As Range
On Error GoTo Err_Filter
oWS = ActiveSheet
oWS.UsedRange.AutoFilter(Field:=2, Criteria1:="Banana")
oRng = oWS.Cells.SpecialCells(xlCellTypeVisible)
oColRng = oWS.Range("A2:A5000")
oInRng = Intersect(oRng, oColRng)
MsgBox("Filtered Range is " & oInRng.Address)
MsgBox("First Row Filtered Range is " & oInRng.Rows(1).Row)
Finally:
If Not oWS Is Nothing Then oWS = Nothing
Err_Filter:
If Err <> 0 Then
MsgBox(Err.Description)
Err.Clear()
GoTo Finally
End If
End Sub
Posts
