Multiple criteria on filter

[ExcelPupper]

I am trying to filter out data by having the criteria: on column D, it should contain either X100 or X101 and data on column E should not contain any of the ff: G1, G2, G3 but the code is having error when I run it.

Sub FilterData
    Dim LR As Long

'
    Sheets(1).Activate
    LR = Range("A" & Rows.Count).End(xlUp).Row

   
    ActiveSheet.Range("A1:D" & LR).AutoFilter Field:=4, Criteria1:="=X101", _
        Operator:=xlOr, Criteria2:="=X100"
    Range("A1").Select
    ActiveSheet.Range("A1:D" & LR).AutoFilter Field:=5, Criteria1:="<>*G1*" _
        , Operator:=xlAnd, Criteria2:="<>*G2*", Operator:=xlAnd, Criteria3:="<>*G3*"
End Sub

 

[jasonb75]

You can only have 1 or 2 criteria, the parameter Criteria3 is not valid.

Your range is set to A1:D & lr (4 columns) so there is no Field / column 5.

The easiest way to achieve what you want is with a helper column to filter on. Assuming column F is empty, enter the formula below in F2, array confirm it with Ctrl Shift Enter and fill down, then filter to show only rows which are TRUE in that column.

=AND(OR(D2={"X100","X101"}),ISERROR(SEARCH({"G1","G2","G3"},E2)))

If desired, you could capture the formula entry with the macro recorder, then use that with your filter code to enter the formula when needed.

 

[DanteAmor]

ActiveSheet.Range("A1:D" & LR).AutoFilter Field:=5, Criteria1:="<>*G1*" _ , Operator:=xlAnd, Criteria2:="<>*G2*", Operator:=xlAnd, Criteria3:="<>*G3*"

As Jason comments, that way you can only filter up to 2 criteria ..
But you can put the data that does not contain G1, G2 and G3 in an array and filter by values:

Remember to put the range up to column E to filter by column 5.

Sub FilterData()
    Dim lr As Long, i As Long
    Dim dic As Object
    
    Set dic = CreateObject("Scripting.Dictionary")
    Sheets(1).Activate
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
      If Not Range("E" & i) Like "*G1*" And Not Range("E" & i) Like "*G2*" And _
         Not Range("E" & i) Like "*G3*" Then
          dic(Range("E" & i).Value) = Empty
      End If
    Next
    ActiveSheet.Range("A1:E" & lr).AutoFilter 4, "=X101", xlOr, "=X100"
    ActiveSheet.Range("A1:E" & lr).AutoFilter 5, dic.keys, xlFilterValues
End Sub

 

[ExcelPupper]

As Jason comments, that way you can only filter up to 2 criteria ..
But you can put the data that does not contain G1, G2 and G3 in an array and filter by values:

Remember to put the range up to column E to filter by column 5.

Sub FilterData()
    Dim lr As Long, i As Long
    Dim dic As Object
   
    Set dic = CreateObject("Scripting.Dictionary")
    Sheets(1).Activate
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
      If Not Range("E" & i) Like "*G1*" And Not Range("E" & i) Like "*G2*" And _
         Not Range("E" & i) Like "*G3*" Then
          dic(Range("E" & i).Value) = Empty
      End If
    Next
    ActiveSheet.Range("A1:E" & lr).AutoFilter 4, "=X101", xlOr, "=X100"
    ActiveSheet.Range("A1:E" & lr).AutoFilter 5, dic.keys, xlFilterValues
End Sub

May I just ask what is the function of this line

  Set dic = CreateObject("Scripting.Dictionary")

Im not really that well versed on using excel vba thanks! ?

 

[DanteAmor]

May I just ask what is the function of this line

Set dic = CreateObject("Scripting.Dictionary")

In this case the macro uses it to store the unique values in column E that do not meet the criteria.
Here more information about the vba-dictionary:

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

This post contains everything you need to know about using the VBA Dictionary. There are tons of code examples including a real world application.

excelmacromastery.com