VBA Filter Out Value

[nyrangers1994]

I have an Excel file with a table (Listobject) that has a column called "Project". In the Project field, there are many varying values and the list is always expanding. Some records in the table have a value for Project called "N/A" and I'm trying to create a macro that automatically filters out any records with N/A without the user having to click the filter button and unchecking N/A.

When I tried recording a macro to see what the basic code would be, I saw that it provides an array for the criteria of the values that should be in, not the ones that should be filtered out:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:= _
Array("Project 1", "Project 2", "Project 3", "Project 4", "Project 5", _
"Project 6", "Project 7"), Operator:=xlFilterValues

The problem I have though is that only the N/A values should be filtered out, but everything else should stay in, and these records that should stay in will always have an expanding and varying list of Projects. To avoid continually having to keep the macro up to date, is there a macro I can create that filters out the "N/A" as opposed to filtering in everything else?

Thanks for your help.

 

[Firefly2012]

You can use a custom filter:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:="<>N/A"

 

[nyrangers1994]

Thanks!

 

[Firefly2012]

You're welcome

 

[DeepikaShinde]

Thanks...
this helped me also

You can use a custom filter:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:="<>N/A"

 

[kamalm]

what if I have more than 2 values that need to be filtered out ?

 

[Eddie1212]

what if I have more than 2 values that need to be filtered out ?

Did you find an answer? I'm sitting with a similar problem. Adding an xlAnd nor xlOr with another Criteria doesn't seem to work. And also not writing both in the same criteria don't seem to work either

 

[Burning Fish]

Did you find an answer? I'm sitting with a similar problem. Adding an xlAnd nor xlOr with another Criteria doesn't seem to work. And also not writing both in the same criteria don't seem to work either

Adding a Criteria2 entry worked for me. In my case:

ActiveSheet.Range("A1").AutoFilter Field:=3, Criteria1:="<>99004", Criteria2:="<>99008"

 

[Eddie1212]

Adding a Criteria2 entry worked for me. In my case:

ActiveSheet.Range("A1").AutoFilter Field:=3, Criteria1:="<>99004", Criteria2:="<>99008"

Thanks Burning Fish. Can't remember what I did, but I managed to find a work-around in my particular case.

The problem comes in when you are filtering on one value in criteria1 (in the your quoted case, say 99*), but want to filter out two or more of the results (eg 99004 and 99008) , thus ending up with a third criteria, which doesn't seem possible.