VBA - Is it possible to filter out multiple (>2) values?

Judge5

New Member
Joined
Apr 29, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Relatively new to here and VBA, but I've looked through a lot of forums already and can't seem to find an answer to my particular question - is it possible to use a macro-driven filter to exclude multiple (>2) values in a single column? I have a report that runs daily and will include names of varying countries. I want to remove certain countries from the list based on a predetermined list (this is static data that I can add directly in VBA; it doesn't need to be from another sheet or column).

I've tried a simple AutoFilter that works for 2 criteria:
ActiveSheet.Range("C:C").AutoFilter Field:=1, Criteria1:="<>Austria", Operator:=xlAnd, Criteria2:="<>Belgium"

But as soon as I add in a 3rd criteria, I receive a run-time error '1004':
ActiveSheet.Range("C:C").AutoFilter Field:=1, Criteria1:="<>Austria", Operator:=xlAnd, Criteria2:="<>Belgium", Operator:=xlAnd, Criteria3:="<>Denmark"

I then tried using an array, but it only worked for values i wanted to include in the filter data (not exclude). It's possibly my coding was invalid though, but want to see if other people have worked through this issue before wasting any more time than I already have on this.

Would appreciate any help!!

Thank you,
Joe
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,608
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
You can only filter on more that 2 values when using an exact match.
One option is to add a formula to the right of your data, that show true or false, depending on whether you want that row visible & filter on that.
Another option would be to look at advanced filters.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,658
Messages
5,637,618
Members
416,976
Latest member
LL1300

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top