EXCEL 2013 FILTER MULTIPLE NOT EQUAL TO CRITERIA

Hi
See the attached file.
I am trying to write a Macro to:
Filter to display rows where IF NOT EQUAL to the following in Column D:  TKT, HTL, CAR, SFE
Other data in the D column may vary.
I wrote this but it didn't work!!!:
Application.Goto Reference:="Sheet1'!R1C1"
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$Z$5000").AutoFilter Field:=4, Criteria1:= _
        "<>TKT", Operator:=xlAnd, Criteria2:="<>HTL", Operator:=xlAnd, Criteria3:="<>CAR", Operator:=xlAnd, Criteria4:="<>SFE"

Gratefully,

Gail (in Australia)
GAILBAKERAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GAILBAKERAuthor Commented:
Hi again - anyone got any ideas on the above??
0
Rgonzo1971Commented:
Hi,

you can only have 2 criterias

Regards
0
GAILBAKERAuthor Commented:
How crazy is that????!!!!
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Glenn RayExcel VBA DeveloperCommented:
Not too crazy.  However, there is a workaround in VBA.  Try this:
Sub Filter4()
    Dim Dic As Object
    Dim rng As Variant
    Dim x As Long
    Dim arrFilter As Variant
    
    rng = Range("D2:D" & Cells.SpecialCells(xlLastCell).Row).Value
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = vbTextCompare
    
    For x = 1 To UBound(rng, 1)
        If rng(x, 1) <> "TKT" And rng(x, 1) <> "HTL" And _
            rng(x, 1) <> "CAR" And rng(x, 1) <> "SFE" Then
            Dic.Item(rng(x, 1)) = rng(x, 1)
        End If
    Next x

    'assign Dic to array then filter
    arrFilter = Dic.Items
    ActiveSheet.Range("$A$1:$Z$" & Cells.SpecialCells(xlLastCell).Row).AutoFilter Field:=16, Criteria1:=Array(arrFilter), _
        Operator:=xlFilterValues

End Sub

Open in new window


This code creates a Dictionary item that will contain a list of all unique values in column D - EXCEPT for your four specified strings (TKT, HTL, CAR, SFE).  It then assigns that list to an array and filters the data on that array.

I tested this with a table of 15000+ rows and 131 unique values in a filtered column and it ran almost instantaneously.

Regards,
-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GAILBAKERAuthor Commented:
That is extremely clever Glenn - many thanks for taking the trouble to work it out.

I have actually made a workaround which was to copy the D column to F column, then execute a filter for TKT and HTL in F column, and then execute a filter for CAR and SFE in D column.

A very amateur method, but it worked!!

I will give you the points for your solution, cheers, Gail
0
GAILBAKERAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for GAILBAKER's comment #a40192781

for the following reason:

Very clever and lateral thinking
0
Tom AcroCommented:
Hi Glenn,

nice little script you wrote there! Very helpfull, even after two years.
Thanks :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.