AutoFilter with multiple criteria in a single column

Hi, Faridwahidi,

please do not start multiple posts on the same subject [http://www.excelforum.com/excel-prog...haracter.html] but stay in the original thread or at least post a link to the other thread.

Ciao,
Holger

Did you try it this way?

Sub FilterMultiple()
   
    Dim rg As Range, i As Long
       i = Range("G" & Rows.Count).End(xlUp).Row
        
    Set rg = Range("A12:TB" & i)
With rg        
        .AutoFilter Field:=2, Criteria1:="Business Banking"
        .AutoFilter Field:=6, Criteria1:=Array("BC, HEA*", "Deputy Hea*", "Head, Bi*")
End With    
End Sub

HI Xladept,

I have tried, but there is only ("Head, Bi*") selected while the 1st & 2nd character ("BC, HEA*", "Deputy Hea*") not selected.

I do not expect that filter would be a bit difficult task. If there is no star "*" sign, it works fine. we can use record macro code


This code working fine

With Worksheets("ORIGINATOR")
    .AutoFilterMode = False
    .Range("A12:TB" & i).AutoFilter Field:=2, Criteria1:="Business Banking"
    .Range("A12:TB" & i).AutoFilter Field:=6, Criteria1:=Array("BC, HEAD", "Deputy Head", "Head, Biz Dev"), Operator:=xlFilterValues
End With

but will NOT working for filtering more than 2 items with star "*" sign

 With Worksheets("ORIGINATOR")
    .AutoFilterMode = False
    .Range("A12:TB" & i).AutoFilter Field:=2, Criteria1:="Business Banking"
    .Range("A12:TB" & i).AutoFilter Field:=6, Criteria1:=Array("BC, HEA*", "Deputy Hea*", "Head, Bi*"), Operator:=xlFilterValues
 End With

If you can post a sample workbook, I'll take a look at it tomorrow.

Hi Farid.

I think I'm confused - when it filters the first criterion of the array, why would there be other criterion to filter on??

Hi Farid,

I couldn't see anything wrong with your code, but here's a workaround:

Sub FilterMultiple()
   ActiveSheet.AutoFilterMode = False
Dim rg As Range, i As Long, j As Long
i = Range("G" & Rows.Count).End(xlUp).Row

    Set rg = Range("A12:TB" & i)
    
        rg.AutoFilter Field:=2, Criteria1:="Business Banking"
        For j = 13 To i
        With rg.SpecialCells(xlCellTypeVisible)
If Cells(j, 6) Like "BC, HEA*" Or _
        Cells(j, 6) Like "Deputy Hea*" Or _
                Cells(j, 6) Like "Head, Bi*" Then GoTo GetNext
        Cells(j, 6).EntireRow.Hidden = True
        End With
GetNext:        Next j: End Sub

Hi Farid,

Thanks for the rep!

j is the index for the For...Next Loop - the values start at 13 (the first data row) and go all the way to i

BTW: If you want to recover between runs I should have added the code in red:

Sub FilterMultiple()
   ActiveSheet.AutoFilterMode = False:Rows.Hidden=False
Dim rg As Range, i As Long, j As Long
i = Range("G" & Rows.Count).End(xlUp).Row

    Set rg = Range("A12:TB" & i)
    
        rg.AutoFilter Field:=2, Criteria1:="Business Banking"
        For j = 13 To i
        With rg.SpecialCells(xlCellTypeVisible)
If Cells(j, 6) Like "BC, HEA*" Or _
        Cells(j, 6) Like "Deputy Hea*" Or _
                Cells(j, 6) Like "Head, Bi*" Then GoTo GetNext
        Cells(j, 6).EntireRow.Hidden = True
        End With
GetNext:        Next j: End Sub

You can't use wildcards with an array filter, if you have more than two values in the array, which was the original problem.

You'd have to loop and create an array of the actual values you are interested in, or use an advanced filter instead.

Hi Rory,

Is it that:

Operator:=xlFilterValues

is poorly coded??

I guess that depends on your perspective.

It's expecting exact values but it is willing to accept two 'contains' criteria, just as you could do through the UI.