After long time let us revisit our good old Autofilter Fruits example. The following figure shows the data available
If you need to filter say Oranges and Apples alone, you can either pass both criteria (Yes! I have avoided using - Mutliple criteria) or can try using an Array where you can pass multiple values
Sub AutoFilter_Using_Arrays()
Dim oWS As Worksheet
On Error GoTo Err_Filter
Dim arCriteria(0 To 1) As String
Set oWS = ActiveSheet
arCriteria(0) = "Apple"
arCriteria(1) = "Orange"
oWS.UsedRange.AutoFilter Field:=2, Criteria1:=arCriteria, Operator:=xlFilterValues
Finally:
If Not oWS Is Nothing Then Set oWS = Nothing
Err_Filter:
If Err <> 0 Then
MsgBox Err.Description
Err.Clear
GoTo Finally
End If
End Sub
If you leave out the Operator in Excel VBA Autofilter- Only Last Value of the Array Will be displayed
You can also pass the values directly like:
oWS.UsedRange.AutoFilter Field:=2, Criteria1:=Array("Apples","Peaches","Grapes), Operator:=xlFilterValues
Posts
Excelent tip
ReplyDeleteNo good, the top one is bunk. It wants a constant... which of course is never going to be constant except for some dream world.
ReplyDeleteCan array be replaced with a range
ReplyDeleteI am trying to write something similar on 2003 version and it is not working. Is there an alternative?
ReplyDeleteUnfortunately the only way to get around "Constant expression required" error is to choose a constant that will cover your max length of the array... I used 100 :) for a range with 10 entries...
ReplyDeleteAnd below a piece of my code which uses variable ranges and different worksheets:
Sub AutoFilter_Using_Arrays_var_rng()
Dim i As Long
'select the sheet containing the filter values,
in this case I named it 1
Sheets("1").Select
'set the lenght of the array
'I only needed 10 values so 100 covers pretty much that
Dim arCriteria(0 To 100) As String
'set each cell in the range as array member
'my data was in col "D" so I didn't bother to change that
For i = 2 To [d999].End(xlUp).Row
arCriteria(i - 2) = Cells(i, "d") '"Apple"
Next i
'select the sheet whre I need to apply the filter
'named it "2"
Sheets("2").Select
'set the filter for values in column "D" - pure coicidence- column is identified through "field:=4"
With [a1]
.AutoFilter field:=4, Criteria1:=arCriteria, Operator:=xlFilterValues
End With
End Sub
Of course the credit for the original filtering goes to shasur, I only added a bit of "customization" so you won't have to edit the code each time, but simply change the values in the range...
Watch out for my web site coming soon :)
BR
GM - appasionate VBA developer
From the Netherlands: brilliant code!
Deleteis there a way to tell here to read values from worksheet
ReplyDeletearCriteria(0) = "sheet2.range("b1")
arCriteria(1) = "sheet2.range("b2")
something like tht thanks
Thanks for your Post. it helped me a lot
ReplyDeleteHello why would the following using <> not work?
ReplyDeleteoWS.UsedRange.AutoFilter Field:=2, Criteria1:=Array("<>Apples","<>Peaches"), Operator:=xlFilterValues