• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Advanced Filter using multiple criteria AND/OR

D

DennisR

New Member
I've found lots online about how to use the advanced filter with multiple criteria, but I am running into a bit of a problem. I am able to get my advanced filter working correctly when I use just one AND filter, but I cannot get my OR filter to work properly.

I have three fields: Group, Date, Status

Status can be open or closed. I want my advanced filter to be able to sort by all 3 fields together or sort by Status only, i.e. ALL open or ALL closed (Group and Date fields would be blank). I am using a dropdown box with a named range to allow the user to make their selection. I am using two lines in my advanced filter for this OR operation. I can get my filter to do either one of these scenarios correctly, but not both together. Is there a way to fix this? Can excel do what I am asking? I assume it can.

Do I need to make two separate filters to get this functionality or is just one filter enough? I am using a command button to allow the user to apply the filter. So if I need two separate filters, then I would need an additional button, which I am hoping to avoid.
 
Last edited:
Post us an example of what you're having issues with - you'll get a quicker and better response ....
FWIW, I find AND/OR issues to be counter intuitive!
 
Attached is the sample file of what I'm trying to do with a description next to each filter line of what I'm trying to accomplish.
 

Attachments

  • Sample.xlsm
    38.1 KB · Views: 6
I find that formula generated blanks are not treated as blanks. Use vba to fill criteria range.

I've added Worksheet_Change event code for "Filter" sheet.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C6:C8")) Is Nothing Then
    With Sheets("Import")
        .Range("F2").Value = Range("C6").Value
        .Range("G2").Value = Range("C7").Value
        .Range("H2").Value = Range("C8").Value
    End With
End If
End Sub

Then modified your FilterData() code to...
Code: 
Sub FilterData()
Sheets("Filter").Select
Range("B10").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
Sheets("Import").Range("Table2[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Sheets("Import").Range("F1:H2"), Unique:=False
    Sheets("Import").Range("Table2[#All]").Cells.SpecialCells(12).Copy Destination:=Sheets("Filter").Range("B10")
Columns.AutoFit
Range("B10").Select
End Sub

Note that you had column headers (in criteria) that's not exact match to the source table.

To filter for all 3 criteria, choose all 3 criteria from drop down.
To show all Open status, delete value from C6 & C7, and choose Open for C8.
Do same for all Closed status, just choose Closed for C8.

Technically you don't need "Show All". If you remove all selection from C6:C8 range, it will remove filter and show all records.
 

Attachments

  • Sample (1).xlsm
    32.7 KB · Views: 7
This does exactly what I wanted. I guess my problem was not using vba to fill the criteria range. My headers in my real document are correct, but messed it up in trying to make the sample file. Thanks for the help.
 
So I have an additional question now, because things seem to be acting funny and I can't figure out what the problem is. It appears I am having a memory cache problem. When I run my code to open many files and combine them into my master file and then try to run my filter, it doesn't work. If I wait for a while, say 5 minutes, the filter works correctly again. Is there a way to get around this? Like clear the cache or something? Thanks.
 
Last edited:
Any idea what might be causing this? It seems like a weird problem to debug. Maybe I can code a program to flush the memory?
 
So I actually tried and replicated this same scenario on both of my computers; home and office. My home computer is running windows 10 and is a desktop. My office computer is a windows 7 laptop. Both show Excel using about 21 MB of memory, so I guess it isn't actually a memory problem, but for some reason it works correctly when I just reopen my excel (on both machines). My Office clipboard is clear. Nothing is being stored there. I edited my code so it isn't using the clipboard now. That change didn't have any effect on my problem. I am really not sure what's happening.

As long as I don't import my data, the filter works properly every time once I reopen excel after the original import. It's only just after I import my data that it gives me problems.
 
Only other guess that I have would be... to check if there is any routine that exits sub while Application.ScreenUpdating etc is turned off.
 
Ah yes. That did the trick. I just had to turn
Application.EnableEvents and Application.ScreenUpdating back on.
 
You must log in or register to reply here.
Back
Top