Tuesday, July 27, 2010

Excel VBA Autofilter - Specify Multiple Criteria using Array

How to pass an Array as Criteria in Excel Autofilter - VBA

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

9 comments:

  1. Anonymous7:52 AM

    Excelent tip

    ReplyDelete
  2. Anonymous10:34 AM

    No good, the top one is bunk. It wants a constant... which of course is never going to be constant except for some dream world.

    ReplyDelete
  3. Can array be replaced with a range

    ReplyDelete
  4. Anonymous10:59 AM

    I am trying to write something similar on 2003 version and it is not working. Is there an alternative?

    ReplyDelete
  5. Anonymous1:02 PM

    Unfortunately 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...
    And 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

    ReplyDelete
    Replies
    1. JP vd B12:42 AM

      From the Netherlands: brilliant code!

      Delete
  6. is there a way to tell here to read values from worksheet

    arCriteria(0) = "sheet2.range("b1")
    arCriteria(1) = "sheet2.range("b2")


    something like tht thanks

    ReplyDelete
  7. Thanks for your Post. it helped me a lot

    ReplyDelete
  8. Sub ArrayAutofilterFromNamedRange()

    Dim oWS As Worksheet
    Dim oRange As Range
    Dim numrows As Integer
    Dim i As Integer
    Dim arCriteria(0 To 100) As String

    On Error GoTo Err_Filter

    Set oWS = ActiveSheet
    Set oRange = ActiveWorkbook.ActiveSheet.Range("mydynamicrange")
    numrows = oRange.Rows.Count
    i = 0

    For Each Row In oRange
    arCriteria(i) = Row.Value
    i = i + 1
    Debug.Print arCriteria(i)
    Next Row

    oWS.Range("B1").AutoFilter Field:=1, Criteria1:=arCriteria, Operator:=xlFilterValues


    Finish:
    If Not oWS Is Nothing Then Set oWS = Nothing


    Err_Filter:
    If Err <> 0 Then
    MsgBox Err.Description
    Err.Clear
    GoTo Finish
    End If

    End Sub

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group