Excel AutoFilter With Criteria in a Range

Excel AutoFilter With Criteria in a Range

In Excel 2003, and earlier versions, an AutoFilter allows only two criteria for each column. In Excel 2007 and later, you can select multiple criteria from each column in the table. See how to apply an Excel AutoFilter with  multiple criteria in a range on the worksheet.

Update: Get the latest version of this workbook on my Contextures site: Filter Criteria List Macro.

AutoFilter Criteria

In Excel 2003, and earlier versions, if you wanted to filter for multiple criteria, you had to use an Advanced Filter.

To prepare for an Advanced Filter, list all the criteria on a worksheet, and then use that list (and its heading cell) as the criteria range.

In Excel 2007 and Excel 2010, the AutoFilter feature has been improved, and you can select multiple criteria in each column.

autofiltermulti

Record an AutoFilter Macro

If you record a macro while selecting criteria in Excel 2007, it will look something like this:

autofiltermulti02

The criteria are entered as an array, showing all three items that were selected in the drop down list.

Create Your Own Array

In the Contextures mail bag this week, someone asked if it’s possible to create this type of AutoFilter criteria array from a list on the worksheet. And the answer is yes, you can!

  • NOTE: This criteria list technique works for exact matches only. You can’t use wildcards in the criteria items, such as *apple*
  • Using wildcards forces Excel to apply a Custom AutoFilter, “contains”, which is limited to 2 criteria.

For this example, there’s a dynamic named range — CritList — on the Lists worksheet. The items in the CritList range will be used as the AutoFilter criteria array.

autofiltermulti03

On the Orders sheet, the fourth column — Products — will be filtered using this criteria list.

autofiltermulti04

AutoFilter Code With Array

In an Excel VBA procedure, you can create a variable to store the values from the CritList named range. Define this variable as a Variant, and it will store the values as an array.

vCrit = rngCrit.Value

Then, to use this variable as the AutoFilter criteria list, transpose the array, so it’s read as a row, instead of a column. If you don’t transpose the array, only the first item would be used in the criteria array. (Or, create your worksheet list in a row, instead of a column, and you won’t have to transpose it.)

Criteria1:=Application.Transpose(vCrit)

Here’s the complete code for the AutoFilter:

Sub FilterRangeCriteria()
Dim vCrit As Variant
Dim wsO As Worksheet
Dim wsL As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range
Set wsO = Worksheets("Orders")
Set wsL = Worksheets("Lists")
Set rngOrders = wsO.Range("$A$1").CurrentRegion
Set rngCrit = wsL.Range("CritList")
vCrit = rngCrit.Value
rngOrders.AutoFilter _
    Field:=4, _
    Criteria1:=Application.Transpose(vCrit), _
    Operator:=xlFilterValues
End Sub

Download the AutoFilter Array Sample File

Update: Get the latest version of this workbook on my Contextures site: Filter Criteria List Macro.

To see the sample code and the named range, download my AutoFilter Criteria Array sample file.

The file is in xlsm format, zipped, and you can enable macros when you open the file.
_________

37 thoughts on “Excel AutoFilter With Criteria in a Range”

  1. This is very useful code. Thank You.

    However, why does this fail when the named range contains more than 2 values with wildcard?

    {“*oranges*”;”*apples*”;”*grapes*”}

    1. Did you ever figure out why you can’t have 3 or more items in the array if they contain wildcards?
      I have the same problem, and can find no answers.
      Here is the code I am trying:
      ActiveSheet.Range(“$A$3”).CurrentRegion.AutoFilter Field:=2, Criteria1:=Array(“Block*”, “Cell*”, “Grid*”), Operator:=xlFilterValues
      I have also tried this:
      ActiveSheet.Range(“$A$3:$A$3”).AutoFilter Field:=2, Criteria1:=Array(“Block*”, “Cell*”, “Grid*”), Operator:=xlFilterValues

  2. This works perfectly, however, I have one minor problem and one question:
    1. My CritList contains numbers and these do not get included in filter, how can I do this?
    2. In addition to the range that I have in my CritList I want to remove the blank rows. How can I add this to my CritList?
    Thanks for this code… very useful!
    Steve

    1. I find exactly the same problem as described in the post of Steven Mulvenna dated Nov. 8, 2012. So far I could not figure out how to solve it. Have you gotten any solution? Any help would just be great. Thanks in advance.

  3. How would you return the autofilter Criteria1 if the filter is set on a date. In Excel 2003, there is no problem. However, in 2007+, dates get grouped on year, month, date. This is quite a pain to return the Criteria1. It shows an Application-defined or Object defined error. Text values are no hassle, dates are defintely a big problem.

    1. Never mind my query. I found a work around. To disable the dates in the AutoFilter from getting grouped, you need to go to File > Options > Advanced > Display Options For This Workbook, and make sure the Group Dates in the AutoFilter Menu is off. This will list the dates normally in the dropdown. Then Criteria1 and Criteria2 values get populated.

  4. how can i use the array in a conditon that i have two columns with filter condition example:
    column A:selected# 1,2,3 and in column B i dont want to select that are equal to column A: 1,2,3. but the number in column B is not permanent meaning later on other numbers might be added and i want to capture it using the array.
    my logic conditon: Column A: 1,2,3 selected and Column B: does not equal to Array Column A.. im having problem in using array does not equal in more than 2 criteria using the filter funtion. im getting syntax error.
    Syntax Error:
    ActiveSheet.Range(“$A$7:$U$5974”).AutoFilter Field:=3, Criteria1:=Array( _
    “770”, “773”, “778”, “779”), Operator:=xlFilterValues
    ‘ActiveSheet.Range(“$A$7:$U$5974”).AutoFilter Field:=4, Criteria1:=Array( _
    ‘ “770”, “773”, “778”, “779”). Operator:= _
    xlFilterValues

  5. Syntax Error:
    ActiveSheet.Range(“$A$7:$U$5974”).AutoFilter Field:=3, Criteria1:=Array( _
    “770”, “773”, “778”, “779”), Operator:=xlFilterValues
    ActiveSheet.Range(“$A$7:$U$5974”).AutoFilter Field:=4, Criteria1:=Array( _
    “770”, “773”, “778”, “779”). Operator:= _
    xlFilterValues

  6. Syntax Error:
    ActiveSheet.Range(“$A$7:$U$5974”).AutoFilter Field:=3, Criteria1:=Array( _
    “770”, “773”, “778”, “779”), Operator:=xlFilterValues
    ActiveSheet.Range(“$A$7:$U$5974”).AutoFilter Field:=4, Criteria1:=Array( _
    “770”, “773”, “778”, “779”). Operator:= _
    xlFilterValues
    the does not symbol was immited when i paste it here.

  7. Good morning,
    I am trying to filter a range of duration data where the value falls between two hard coded values.
    What I would like to do is to be able to replace the hard coded values either with Constants or with references to worksheet cells.
    Whilst I can refer to a worksheet cell providing that I’m doing an “=”, I can’t do a >= or a 0″, Operator:=xlAnd, Criteria2:=”0″, Operator:=xlAnd, Criteria2:=FiveMinutes THIS REFERENCE TO A CONSTANT DOESN’T!
    ActiveSheet.Range(“Database”).AutoFilter Field:=FilterField, Criteria1:=”>0″, Operator:=xlAnd, Criteria2:= Range(“FiveMinutes”).Value THIS WORKS
    ActiveSheet.Range(“Database”).AutoFilter Field:=FilterField, Criteria1:=”>0″, Operator:=xlAnd, Criteria2:= ” Range(“Database”).Columns.Count Then ‘only if at least one row displayed
    .Interior.ColorIndex = 43
    .Font.ColorIndex = 1
    End If
    ‘5 to 10 minutes Yellow
    ActiveSheet.Range(“Database”).AutoFilter Field:=FilterField, Criteria1:=”>0.00347222″, Operator:=xlAnd, Criteria2:=”FiveMinutes”, Operator:=xlAnd, Criteria2:=” Range(“Database”).Columns.Count Then ‘only if at least one row displayed
    .Interior.ColorIndex = 6
    .Font.ColorIndex = 1
    End If
    [/code]
    Thanks in advance for your assistance.

  8. I have three workbooks whose data is linked to a master workbook. If I add an item to the master and then sort alphabetically it sorts in all of the lined workbooks which is perfect. There is one issue some of the data in one of the linked workbooks is data that is manually entered in that workbook and when I sort in the master that data doesn’t sort along with the linked data. Is there a way to fix this issue?

  9. how to fix the if contion on below criteria1 in vba code.
    ActiveSheet.Range(“$A$1:$O$10000”).AutoFilter Field:=14, Criteria1:=Array( _
    “BODY”, “BODY BANK”, “PAINT”, “PRE-TRIM”, “TCF”), Operator:=xlFilterValues

  10. I tried changing the field that is reference from 4 to 1, but i guess the code can only do the fourth field of the table? if not, how can i fix this?

  11. for example
    Sub FilterRangeCriteria()
    Dim vCrit As Variant
    Dim wsM As Worksheet
    Dim wsL As Worksheet
    Dim rngCrit As Range
    Dim rngOrders As Range
    Set wsM = Worksheets(“MfrBodyCode”)
    Set wsL = Worksheets(“Lists”)
    Set rngOrders = wsM.Range(“$A$3”).CurrentRegion
    Set rngCrit = wsL.Range(“CritList”)
    vCrit = rngCrit.Value
    With wsO
    rngOrders.AutoFilter _
    Field:=4, _
    Criteria1:=Application.Transpose(vCrit), _
    Operator:=xlFilterValues
    End With
    End Sub
    This is not working for me for some odd reason. I’ve been staring at it all day.

    1. @Garrett, did you create named ranges “CritHead” and “CritList”?
      In the code, you’ve added “With wsO” and “End With” — you don’t need those lines, and you don’t have a variable named wsO.
      On the MfrBodyCode sheet, make sure that the first column is included in the filtered range. Try removing the filter, then adding it again.

    2. @Garrett
      first “Save” the entire book onto your desktop as a “macro-enabled” file.
      Then, open it and accept “Macro-Enabled” option if you are asked, when you open it up.
      Then it should work. Try it. If it doesn’t work, post your code here and I will look at it.

  12. Hi…
    Is there any way to pass the criteria as parameters?
    I mean, if the criteria are selected from a listbox (one or more) is there any way to have the criteria specified as variables?
    thanks in advance

  13. HarryS
    ‘thanks for for BLOG
    ‘ maybe this may help clarify some of the confusion
    ‘ The problem with operator 7 xlFilterValues is that they use operators as
    ‘ 0 if one
    ‘ 2 an OR if two
    ‘ 7 and an array if more than two selected
    ‘ the arrays and criteria have a lot of” =” that confuse excel when something like “=fredsFarm” is put in a cell
    ‘ so use as “#” & “>=4567 to store and mid(.. , 2 ) to retrive
    ‘ rather than use arrays as we had to in ancient times use the functions Join and Replace
    ‘ that way we can save and reload criteria into and from different ranges
    ‘ save the criteria in range RasFilt from the filter range RaFoFi
    ‘ the . are With RaFoFi
    Case 7 ‘, xlFilterValues
    ‘will work on one or two only then an array
    ‘..1 is a 0 operator
    ‘ 2 as is as an OR operator ‘xlOR
    ‘ any more it is as an array .. with = ..
    ‘ using # to replace = to allow storage in a ONE cell
    RaSFilt(Fi, 4) = .Operator
    If .Operator = 2 Then RaSFilt(Fi, 5) = “#” & .Criteria2
    If IsArray(.Criteria1) Then
    RaSFilt(Fi, 3) = Replace(Join(.Criteria1, “,”), “=”, “#”)
    Else
    RaSFilt(Fi, 3) = “#” & .Criteria1
    End If
    ‘ etc
    ‘ to reload the filter
    With RaFoFi
    For Fi = 2 To RaSFilt.Rows.Count
    F = Fi – 1

    Select Case RaSFilt(Fi, 4) ‘ the operator
    Case 0
    ‘ no operator
    If Mid(RaSFilt(Fi, 3), 2) “” Then .AutoFilter F, Mid(RaSFilt(Fi, 3), 2)
    Case 1, 2 ‘xlAnd, xlOr
    .AutoFilter F, Mid(RaSFilt(Fi, 3), 2), RaSFilt(Fi, 4), Mid(RaSFilt(Fi, 5), 2)
    Case 3 To 6
    ‘ xlTop10Items, xlBottom10Items , xlTop10Percent, xlBottom10Percent
    .AutoFilter F, Mid(RaSFilt(Fi, 3), 2), RaSFilt(Fi, 4)
    Case 7 ‘, xlFilterValues will work on one or two .. it is as an OR
    ‘ these have been done in case 0 .. 2 … else is a variable .criteria
    ‘ RaSFilt(Fi + 20, 3) = Replace(Join(.Criteria1, “,”), “=”, “#”)
    .AutoFilter F, Split(Replace(RaSFilt(Fi, 3), “#”, “=”), “,”), RaSFilt(Fi, 4)

  14. The source file have columns with normal data and two columns with a tables.
    The filter is on non table column, but I need to selet all the columns
    I am getting error
    1004 autofilter method of range class failed
    please help me to resolve the issue

  15. Reviving an old thread…
    I’m trying to do the opposite here, but totally relevant. I need to get a string listing the selected filter criteria for a column. I have code that works great if only 1 or 2 filters are selected, but when 3 filters are selected, it fails. Here is the code I’m using (forgot the original source):
    Function FilterCrit(rng As Range) As String
    Dim Filter As String
    Filter = “{All}”
    With rng.Parent.AutoFilter
    If Intersect(rng, .Range) Is Nothing Then GoTo Finish
    With .Filters(rng.Column – .Range.Column + 1)
    If Not .On Then GoTo Finish
    Filter = .Criteria1
    Select Case .Operator
    Case xlAnd
    Filter = Filter & “, ” & Mid(.Criteria2, 2, Len(.Criteria2) – 1)
    Case xlOr
    Filter = Filter & “, ” & Mid(.Criteria2, 2, Len(.Criteria2) – 1)
    End Select
    End With
    End With
    Finish:
    FilterCrit = Filter
    End Function
    Any ideas? Thanks for the help!

      1. Thanks. I found a good function that works without advanced:
        Function GetFilteredItems(temp As String) As String
        Dim fl As Filter
        Dim ws As Worksheet: Set ws = ActiveSheet
        Dim i As Long: i = 0
        Dim myfilters As String
        For Each fl In ws.AutoFilter.Filters
        If fl.On Then
        If Len(myfilters) = 0 Then
        myfilters = ws.AutoFilter.Range.Offset(0, i).Resize(1, 1).Value
        Else
        myfilters = myfilters & “; ” & _
        ws.AutoFilter.Range.Offset(0, i).Resize(1, 1).Value
        End If
        If fl.Count > 2 Then
        myfilters = myfilters & “: ” & Replace(Join(fl.Criteria1), “=”, “”)
        Else
        myfilters = myfilters & “: ” & Replace(fl.Criteria1, “=”, “”)
        On Error Resume Next
        myfilters = myfilters & ” ” & Replace(fl.Criteria2, “=”, “”)
        On Error GoTo 0
        End If
        End If
        i = i + 1
        Next
        ‘Debug.Print “>” & myfilters
        GetFilteredItems = myfilters
        End Function
        Wanted to share in case it could help someone else!

  16. The original speadsheet is excellent. Can you please exted you example to filter apples, oranges or grapes (from product column) and 60 or 65 (from the units column).
    Thanks (using Excel 2007)

  17. My column to be filtered has result of numbers (calculated via formula). I’ve tried to use Criteria1:=Array(“1″,”2″,”3”), Operator:=xlFilterValues,
    also tried Criteria1:=Array(“‘1″,”‘2″,”‘3”), Operator:=xlFilterValues,
    and tried Criteria1:=Array(1,2,3),, Operator:=xlFilterValues,
    …Neither of the 3 options have resulted in filtering the column properly
    (filter showing 1-3 checked). All items are not checked.

  18. Hi – I was wondering if you could filter a Range variable for later pasting to a sheet. i.e. the variable is in place of using an array in memory ?

  19. Dear Debra
    Nowhere can I find where the Autofilter criteria can be expressed as a variable.
    Hard coding the values in the code iis easy. The real world user wants to enter values
    to filter from a textbox userform.
    This works great:
    Worksheets(“Sheet12”).Range(“A1:A11″).AUTOFILTER Field:=1, Criteria1:=”*last days*”, Operator:=xlOr, Criteria2:=”*latter days*”

    But this does not work:
    Private Sub cmdFINDVAL_Click()
    Dim val1 As String
    Dim val2 As String
    val1 = TextBox1.Value
    val2 = TextBox2.Value
    ActiveSheet.Range(“$A$1:$A$11”).AUTOFILTER Field:=1, Criteria1:= _
    “* val1 * “, Operator:=xlOr, Criteria2:=”*val2*”
    End Sub
    Why not? the variable values are passed to the code, yet Autofilter does not respond
    to the values when they are variables. What can I be doing wrong ? This should be
    straightforward and simple to achieve.
    Please help. email: [email protected]
    Thanks in advance
    cr
    Charles A. Rabalais
    Kingwood, Tx

    1. Thanks, Charles, and I’ve added this note to the article:
      ‘—————-
      • NOTE: This criteria list technique works for exact matches only.
      • You can’t use wildcards in the criteria items, such as *apple*
      • Using wildcards forces Excel to apply a Custom AutoFilter, “contains”, which is limited to 2 criteria.
      ‘————-
      However, you could use an Advanced Filter macro, which allows partial matches.
      There are examples on my Contextures site: Excel Filter Criteria List Macros

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.