Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

EXCEL 2013 FILTER MULTIPLE NOT EQUAL TO CRITERIA

Posted on 2014-07-12
7
Medium Priority
?
7,477 Views
Last Modified: 2016-09-05
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)
0
Comment
Question by:GAILBAKER
7 Comments
 

Author Comment

by:GAILBAKER
ID: 40192699
Hi again - anyone got any ideas on the above??
0
 
LVL 54

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 400 total points
ID: 40192711
Hi,

you can only have 2 criterias

Regards
0
 

Author Comment

by:GAILBAKER
ID: 40192730
How crazy is that????!!!!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 1600 total points
ID: 40192779
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
 

Author Comment

by:GAILBAKER
ID: 40192781
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
 

Author Comment

by:GAILBAKER
ID: 40192785
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
 

Expert Comment

by:Tom Acro
ID: 41784634
Hi Glenn,

nice little script you wrote there! Very helpfull, even after two years.
Thanks :-)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question