Macro to filter and exclude more than 2 criteria

[guigoz]

Hi everyone,

Im trying to set a filter on multiple columns, see my below code, its working expect the one in red for the filter on column 5, it doesn't give me the correct result. The column 5 is a free text (for the status) field and I want to exclude only the ones that do not contain "Closed", "Credit note" and "Cancelled". These 3 criteria are constant and its much easier to only exclude these as the rest will change everytime

Please Login or Register  to view this content.

I also tried with

Please Login or Register  to view this content.

It works well but only for 2 criteria, when I added Operator:=x1And, Criteria3:="<>Cancelled" I got an error message.

Any idea how I can add the 3rd criterion?

[alansidman]

Please use code tags and not quote tags to show your VBA. Please read the forum rules on how to make this happen. I have made the change for you today.

[guigoz]

Anyone able to help please?

[ByteMarks]

One way is to use a dictionary to get unique values, excluding those three. This leaves the values you want which are then put into an array and passed to the filter.

Please Login or Register  to view this content.

[sintek]

Any idea how I can add the 3rd criterion?

Negative filter array is limited to 2 criteria...Can easily achieve...also...via a helper column and filter on this column...
Will need to see your setup though...The attached will give you an idea...

Please Login or Register  to view this content.