Multiple autofilter criteria

Question

I am trying to filter using multiple criteria as shown below but keep getting a run time 1004 error.

Not sure what's going and any help would be appreciated.

    Sheets("sheet1").Select
    Cells.Select
    Selection.AutoFilter


    ActiveSheet.Range("$A:$AS").AutoFilter Field:=42, 
    Operator:=xlFilterValues, Criteria1:=Array( _
    "*j*", "*k*"), Operator:=xlAnd, Criteria2:=Array("*l*", "*m*"), 
    Operator:=xlAnd, Criteria3:=Array("*n*", "*o*")

Answer 1

Yuan, there is a limitation of specifying only 2 pattern-based search criteria on a single column in excel auto filters. Your code is trying to cross that limitation and essentially telling excel to applying 6 string patterns which it cannot handle and hence you are getting the error.

If your requirement is not tied to implementing this using VBA, here is a way to achieve the same end results. Here I have made use of the Advanced Filter feature available on Data tab of the excel ribbon:

1. Setup a list of filter criteria as below:

    Cell A1: Name contains "j", "k", "l"
    Cell A2: =ISNUMBER(SEARCH("j", B7))
    Cell A3: =ISNUMBER(SEARCH("k", B7))
    Cell A4: =ISNUMBER(SEARCH("l", B7))

You can continue to add more conditions in this list. In the above B7 is the location of the first entry in the data column that you would like to filter upon. IN this case I have inserted the above four rows directly above the data table where filtering needs to be applied

2. Setup the data: this is the normal set of data that you are working with. No need to convert this to a "Table"

At this point your setup looks as below: Cells A1:A4 contain the conditions, formula shown for clarity. Cells A6:C12 contain the actual data entries with column headings in Row 6

3. Apply advanced filtering: Click on the Advanced button next to Filter on the Data tab on excel ribbon. Select the list range as the range of cells from A6:C12. Select the Criteria range as the range of cells from A1:A4

Click OK and your filters will be applied.

The final filtered view looks as below: Filtered set of rows

Every time you make changes to the filter conditions in cell A2:A4, you will need to reapply the Advanced filter to take effect.