We can create filters programmatically using Excel VBA (AutoFilter using Excel VBA) and also add multiple criteria to it (Create AutoFilter with Multiple Criteria using Excel VBA). 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
See also:
Create AutoFilter with Multiple Criteria using Excel VBA
AutoFilter using Excel VBA
Check for existence of Filter using Excel VBA
Excel Filter Show All using VBA
Retrieve / Get First Row of Excel AutoFilter using VBA
Can you tell me hpw to get the nect row
ReplyDeleteIts just retrieving all the rows. It is not getting the rows which is specifically filtered.
ReplyDeleteI am having problem using part of the code.
ReplyDeleteoRng = oWS.Cells.SpecialCells(xlCellTypeVisible)
Is Cells suppose to be a object variable? Cause I keep getting an error message that says object not set.
It should be
ReplyDeleteSet oRng = oWS.Cells.SpecialCells(xlCellTypeVisible)
How can I get the range I get as a result converted to an array with the number of all the rows filtered?
ReplyDelete