Multiple criteria on filter

ExcelPupper

Board Regular
Joined
Mar 2, 2020
Messages
101
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,632
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.

VBA Code:
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

Board Regular
Joined
Mar 2, 2020
Messages
101
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
VBA Code:
  Set dic = CreateObject("Scripting.Dictionary")
Im not really that well versed on using excel vba thanks! 😅
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
May I just ask what is the function of this line
VBA Code:
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:

 

Watch MrExcel Video

Forum statistics

Threads
1,119,257
Messages
5,577,011
Members
412,761
Latest member
McCrab
Top