CODEWITHSUNDEEP
excelvbaautofilter
walterpayton34
walterpayton34

Reputation: 35

Trying to use a variable for the criteria in autofilter with "not equal" <> and can not get it to work

I'm using Excel VBA to filter a list using the "not equals" expression. If I use Criteria1:="<>Bob", the code runs perfectly, but if I change "Bob" to a variable, the code does not run.

This works:

ActiveSheet.ListObjects("Rpt_AM_04_Sales_ShipTo").Range.AutoFilter Field:=4, _
    Criteria1:="<>Bob", Operator:=xlFilterValues

This does not work:

Dim Test_Criteria As String
Test_Criteria = "Bob"
ActiveSheet.ListObjects("Rpt_AM_04_Sales_ShipTo").Range.AutoFilter Field:=4, _
    Criteria1:<>Test_Criteria, Operator:=xlFilterValues

I expect the code to return a list that has everyone in it except Bob. Instead, I get an error message just typing in the code:

Compile error
Expected: named parameter

Upvotes: 1

Views: 5375

Answers (1)

Louis
Louis

Reputation: 3632

Incorrect syntax

The code does not work because of incorrect syntax where you specify the criteria. Try this:

Change this:

Criteria1:<>Test_Criteria

To this:

Criteria1:"<>" & Test_Criteria

Complete code:

Dim Test_Criteria As String
Test_Criteria = "Bob"
ActiveSheet.ListObjects("Rpt_AM_04_Sales_ShipTo").Range.AutoFilter Field:=4, _
     Criteria1:="<>" & Test_Criteria, Operator:=xlFilterValues

I hope this helps.

Upvotes: 1

Related Questions