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

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!

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

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.
_________