vba for autofilter with three criteria

[littlepete]

hello all...


as always, i think it will be easy to adjust something...
I had to criteria in my filters, i'm adding a third one:

     If antwoord <> "55" Then
            ActiveSheet.Range("rnggebchr").AutoFilter Field:=18, Criteria1:=antwoord & "*", Operator:=xlAnd
            ActiveSheet.Range("rnggebchr").AutoFilter Field:=18, Criteria2:="<>*0100*", Operator:=xlAnd
            ActiveSheet.Range("rnggebchr").AutoFilter Field:=18, Criteria3:="<>*0101*", Operator:=xlAnd
        Else

I have no idea what could be wrong: the "<> 0101" is the new one, the two first combined worked well...
enjoy !!!

 

[kevin9999]

hello all...


as always, i think it will be easy to adjust something...
I had to criteria in my filters, i'm adding a third one:

     If antwoord <> "55" Then
            ActiveSheet.Range("rnggebchr").AutoFilter Field:=18, Criteria1:=antwoord & "*", Operator:=xlAnd
            ActiveSheet.Range("rnggebchr").AutoFilter Field:=18, Criteria2:="<>*0100*", Operator:=xlAnd
            ActiveSheet.Range("rnggebchr").AutoFilter Field:=18, Criteria3:="<>*0101*", Operator:=xlAnd
        Else

I have no idea what could be wrong: the "<> 0101" is the new one, the two first combined worked well...
enjoy !!!

Could you provide a sample of your data?

 

[Alex Blakenburg]

You are using wildcards ("*") it effectively uses the custom filter dialogue box which only allows 2 criteria.
You will need to use a helper column or Advanced Filter.

 

[kevin9999]

@Alex Blakenburg you are correct in your advice to @littlepete - no more than 2 filter conditions using wildcards. I've always found this limitation annoying, so as well as the 2 options you outlined in post #3, there is another alternative I use in these cases that has no limit to the number of wildcard conditions.

Basically, you create a dictionary where the keys are the values in the column you want to apply the multiple wildcard conditions to. Then you loop through the array of keys - removing those that don't meet the conditions you want (using wildcards where necessary). You then filter the column using the values that remain as an array.

With the OP's example, and without knowing what range "rnggebchr" refers to, or what the value of "antwoord" is, I can only show a demonstration using assumptions, being:
1. the range incorporates columns A:R (1 to 18) with headers in row 1
2. antwoord = 50 (I had to use some value to test the code)

Option Explicit
Sub LittlePete()
    Dim d As Object, c As Range, tmp As String, x, i As Long, ws As Worksheet
    Dim antwoord As String: antwoord = 50   '<~~No idea what 'antwoord' is
    Set ws = ActiveSheet
    Set d = CreateObject("scripting.dictionary")
    x = Application.Transpose(Range("R2", Cells(Rows.Count, "R").End(xlUp)))
  
    For i = 1 To UBound(x, 1)
        d(x(i)) = 1
    Next
  
    For Each c In ws.Range("R2", Cells(Rows.Count, "R").End(xlUp))
    tmp = c.Value
        If d.exists(tmp) And Not (tmp) Like antwoord & "*" Then d.Remove (tmp)  '<~~ 1
        If d.exists(tmp) And (tmp) Like "*0100*" Then d.Remove (tmp)            '<~~ 2
        If d.exists(tmp) And (tmp) Like "*0101*" Then d.Remove (tmp)            '<~~ 3
    Next
    
    With ws.Range("rnggebchr")              '<~~ assumes the named range is columns A:R
        .AutoFilter 18, Array(d.keys), 7
    End With
End Sub