CODEWITHSUNDEEP
excelvbafilter
Deke
Deke

Reputation: 495

VBA Filtering more than 2 Criteria

I'm looking for a simple solution to a filtering issue I have. I'm trying to filter out the following letter combinations in Field 9: RB, RC and RG. I've tried a few ways of doing this. The first way I tried it was:

  Set Bigpic = Workbooks.Open("S:\NFInventory\groups\CID\CID Database\BigPic Files\BigPic 2018.xlsx", , , , , , , , True)
     lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
     Rows("1:1").Select
     Selection.AutoFilter
     ActiveSheet.Range("A1:CU" & lrow).AutoFilter Field:=9, Criteria1:="<>RB", Operator:=xlAnd, Criteria2:="<>RC", Operator:=xlAnd, Criteria3:="<>RG"

This failed, so I tried to use an array but that also seems to be failing. It returns with just RG being filtered while the others remain. Any push in the right direction would be helpful.

 Set Bigpic = Workbooks.Open("S:\NFInventory\groups\CID\CID Database\BigPic 
 Files\BigPic 2018.xlsx", , , , , , , , True)
     lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
     Rows("1:1").Select
     Selection.AutoFilter
     ActiveSheet.Range("A1:CU" & lrow).AutoFilter Field:=9, 
 Criteria1:=Array("<>RB", "<>RC", "<>RG")

Upvotes: 0

Views: 4338

Answers (4)

Frank Shaw
Frank Shaw

Reputation: 1

Couldn't you just create a Filter column and put an if statement in it? Something like:

=IFERROR(FIND("RB",UPPER(C9)),0)+IFERROR(FIND("RC",UPPER(C9)),0)+IFERROR(FIND("RG",UPPER(C9)),0)

and then filter on that column = 0 to exclude all the RB,RC,RGs

Upvotes: 0

ruirodrigues1971__
ruirodrigues1971__

Reputation: 342

If is not a problem to have the criteria in a sheet (can be hide)

enter image description here

In the code you can put the criteria in hide sheet

Range("C1:D9").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Sheets("Sheet2").Range("G1:I2"), Unique:=False

Upvotes: 1

Tom Kennedy
Tom Kennedy

Reputation: 58

I have manually excluded more than 2 criteria by filtering by color. If your table has no fill defined, this code will work.

ActiveSheet.Range("A1:CU" & lrow).AutoFilter Field:=9, Criteria1:=Array("RB", _
    "RC", "RG"), Operator:=xlFilterValues
ActiveSheet.Range("A1:CU" & lrow).SpecialCells(xlCellTypeVisible).Interior.Color = 1 
' 1 is a black fill, change to your preference
ActiveSheet.Range("A1:CU" & lrow).AutoFilter Field:=9, Operator:= _
    xlFilterNoFill
'ActiveSheet.Range("A1:CU" & lrow).Autofilter Field:=9 
'this code will unfilter the range, can be used for testing to confirm everything you  _ 
expected to filter was filtered

Upvotes: 0

user10862412
user10862412

Reputation:

You cannot use more than 2 does not equal. Build a dictionary or arraylist out of the values that are not equal and use that as the array filter with xlfiltervalues.

dim i as long, lr as long, arr as object

set arr = createobject("scripting.dictionary")

with ActiveSheet

     lr = .Cells(Rows.Count, 1).End(xlUp).Row

     for i=2 to lr
         select case ucase(.cells(i, "I").value2)
             case "RB", "RC", "RG"
                 'do nothing
             case else
                 arr.item(.cells(i, "I").value2) = vbnullstring
         end select
     next i

     .Range("A1:CU" & lr).AutoFilter Field:=9, criteria1:=arr.keys, operator:=xlfiltervalues

end with

Upvotes: 5

Related Questions