Vba autofilter how many criteria

It is possible to filter values not equal to some criteria, but only up to two values which doesn't work for you:. In fact, the ConstructFilterValueArray method not function get all distinct values that it found in a specific column and remove all values present in last argument. It's a shame that Microfsoft doesn't have implemented this solution in adding simply a new enum as xlNotFilterValues!

As an innovative alternative to schlebe 's recent answer, I tried to use the Filter function integrated in VBA , which allows to filter out a given search string setting the third argument to False. All "negative" search strings e. A, B, C are defined in an array. I read the criteria in column A to a datafield array and basicly execute a subsequent filtering A - C to filter these items out. Here an option using a list written on some range, populating an array that will be fiiltered. The information will be erased then the columns sorted.

Actually the above code did not work. Hence I given a loop to hide the entire row whenever the active cell had the value that I am searching for. For each cell in selection If cell.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 6 years, 9 months ago.

Active 6 months ago. Viewed k times. Why cant I use this code? It gives this error: Run time error autofilter method of range class failed How can I perform this? Improve this question. Igor F. Add a comment. How to filter data using Excel and VBA methods. Filter by color. How to filter by color using Excel and VBA methods. Filter between two numbers. How to filter between two numbers using Excel and VBA methods. It seems to work fine. I think I used the macro recorder and that's what was generated.

I tried it earlier and it didn't seem to work. Do you know any way of using VBA to filter records based on more than 2 criteria in a column? The following Text Filter command on the filter menu works but only for two criteria:.

If I could specify many more criteria, I'd be pleased. As far as I am aware, you cannot filter on the same column by more than two criteria and like Norie I've never seen an array used as filter criteria before. I think what you would need to do is to move the filtering requirement into an additional column populated by a formula along the lines of:.

Thanks, Richard. This filtering will work in overcoming the two criteria limitation.