CODEWITHSUNDEEP
excelvbscriptautofilter
Mitch Conklin
Mitch Conklin

Reputation: 1

Excel VBA Set AutoFilter as "Dose Not Contain" Using DIM Value

Hey Everyone New Programmer here Seeking help for a bit of help on something I cant seem to find in the forums. The below listed program works perfectly for setting an AutoFilter to filter by the DateCode = ActiveCell.Text Statment

Dim DateCode
DateCode = ActiveCell.Text  'For this instant the active cell contains "T-09"

ActiveSheet.Range("$A$1:$T$10000").AutoFilter Field:=1, Criteria1:=DateCode

However I wish to create a Dose Not Contain filter based on the same DateCode / dim statement?

The below statement is the footprint left behind from the simple macro recorder tool. The statement effectively sets the filter as Dose Not Contain "DateCode"

ActiveSheet.Range("$A$1:$DH$10000").AutoFilter Field:=4, Criteria1:= _
    "<>*DateCode*"

The below statement may be re written as the following and work perfectly the first time. However the problem is that my date code changes every time I execute the script. Which was not a problem until I attempted to Invert the the Logic of AF Criteria1:= 

ActiveSheet.Range("$A$1:$DH$10000").AutoFilter Field:=4, Criteria1:= _
    "<>*T-09*"

Any Help Would be Greatly Appreciated

Upvotes: 0

Views: 6486

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

Just insert your variable into the Criteria1 string:

ActiveSheet.Range("$A$1:$DH$10000").AutoFilter Field:=4, Criteria1:="<>*" & DateCode & "*"

Upvotes: 1

Related Questions