VBA - using code to filter criteria NOT to include in a filter...

[Panthers0027]

Hello,

I use Office 2007.

I have a table, that I'd like to filter in my code. I'm trying to filter out certain names. Using the Macro Recorder I get this:

 ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=3, Criteria1:= _
        Array("Name1", "Name2", "Name3"), Operator:=xlFilterValues

The table has many more names, I just shortened it for this question. I'd like to know how I can simply filter out certain names. i.e. I want to take out "Sponsor" & "Sponsor Dept" as the names.

How can I do this?

Thanks for any help that you can provide!
Andre

 

[asking]

Try ~

ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=3, Criteria1:= _

"<>"Sponsor", Operator:=xlAnd, Criteria2:="<>Sponsor Dept"

 

[Panthers0027]

That works great! But what if I have a 3rd criteria to filter out?

ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=3, Criteria1:= _
        "<>Sponsor", Operator:=xlAnd, Criteria2:="<>Sponsor Dept", Operator:=xlAnd, Criteria3:="<>Sponsor-DB"

With 2 criteria, it worked great, but when I added a 3rd, not so much luck.

Any ideas?

 

[asking]

If you need more than two exclusions it needs to do it in an array as per your first example.

Just run the recorder again and when you click the filter triangle in column 3 ~ de-select the names you don't want, they are in the scroll box under text filters and click OK.
(uncheck the boxes next to the names you don't require)

Cheers

 

[Panthers0027]

Correct, that does work, but still creates one problem for me.

ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=3, Criteria1:= _
        Array("Name1", "Name2", "Name3"), Operator:=xlFilterValues
[\code]
 
If I later add other names, they won't be recognized in the code.  i.e, let's say "Name4" gets added to the data set.  This line of code will still only show Array("Name1", "Name2", "Name3").
 
I actually have about 50 optional names in the filter, I just shortened it above to keep the example short.
 
Let me know if you can think of a solution that allows me to filter out more than 2 names, while being able to maintain the flexibility of being able to add names.
 
Thanks again for all your help here!

 

[MrKowz]

Where would the dataset be located that defines the array?

 

[Panthers0027]

The data set (or names) are in Column C. It's a list of receivables. The list gets updated everytime I run a report off of the central system. I simply copy over the new data everytime I run the report. When I do this, new names may appear.

 

[MrKowz]

I'm not 100% sure if this will work, but try this out:

Dim LR      As Long, _
    rngarr  As Variant
LR = Range("C" & Rows.Count).End(xlUp).Row
rngarr = Range("C1:C" & LR)
ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=3, Criteria1:= rngarr), _
    Operator:=xlFilterValues

 

[asking]

MrKowz

Very interesting to me as I am still learning.
Just want to ask how does that macro exclude the names Panthers want excluded?
How does he enter the names for exclusion or am I just being Dumb?

cheers,
Skinman

 

[Panthers0027]

No such luck. It actually filtered everything out.

Let me know if you guys ever come accross a solution. I'm acually a little surprised that it's not something easy.

Thanks to both of you for your help!

Andre