0

spreadsheet example

I want to AutoFilter column B to filter out the rows with favourite colour as red.

I've managed to automate it as follows and it works fine:

ActiveSheet.Range("$A$1:$B$8").AutoFilter Field:=2, Criteria1:=_
"=BLUE", Operator:=xlOr, Criteria2:="YELLOW", Operator:=xlOr, Criteria3:=_
"=PURPLE", Operator:=xlOr, Criteria4:="WHITE"

I have 4 criteria's. I was wondering if it's possible to use only one criteria that filters out the colour red instead?

jsgunner
  • 41
  • 2
  • 2
    If you record a macro while filtering out the red, it will show you the syntax required: `Criteria1:="<>RED`" – Joe Dec 22 '21 at 14:48
  • Your provided code does not actually work fine. – BigBen Dec 22 '21 at 14:53
  • 1
    @Joe J Thanks man, all sorted! – jsgunner Dec 22 '21 at 14:54
  • @BigBen I may have made some errors in copying the code. i noticed i left out some quotation marks when stating my range. i've sorted it now. – jsgunner Dec 22 '21 at 14:56
  • But you really haven't, right? See the [`Range.AutoFilter`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.autofilter) docs why - it only supports 2 criteria. `Operator:=xlOr, Criteria3:=_ "=PURPLE", Operator:=xlOr, Criteria4:="WHITE"` is all invalid. – BigBen Dec 22 '21 at 14:59
  • *2 criteria parameters. One can pass an array of criteria as explained in the same docs. – BigBen Dec 22 '21 at 15:07

0 Answers0