• 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.

VBA-advanced filter with multiple criteria

Can you copy the file
Then delete most of the data
Save as a *.xlsb file type first
Then post it here?
 
HI Hui,

I did what you said, but when I go to "upload File", I cannot select a file, it does not show up... How can I upload it?
 

Hi !

So, respecting your original workbook design,
post a dummy one smaller or just post your code (with code tags)
and a pic of your worksheet and criterias used for advanced filter …
​
 
File is practically empty. Workbook consists of 3 sheets which you need to solve a problem. I deleted all data. If I send a file without any of the sheets, you won't understand the problem.
 

Where I am your file will be refused …

So create a new workbook respecting same design worksheets
or post elements like I already wrote in my previous post …
​
 


As advanced filters are not difficult to use, what is the issue ?
Don't forget to explain the need, the purpose, …

​
 
One of the issue is that you use formula to filter. In some instances Advanced Filter has issue interpreting formula result value. I have not been able to pinpoint the cause, but have been able to work around it.

What you'd do is instead of using formula in your criteria range, use VBA to carry over the info from C6:I6 into criteria range of M7:S7.

Use something like below and assign it to a button.
Code: 
Sub MoveCriteria()
Dim ws As Worksheet
Dim oRange As Range
Set ws = Sheets("FilterData")

With ws
For i = 3 To 9
    If .Cells(6, i) = "" Then
        .Cells(7, i + 10) = ""
    ElseIf i = 3 Then
        .Cells(7, i + 10) = ">" & .Cells(6, i)
    ElseIf i = 4 Then
        .Cells(7, i + 10) = "<" & .Cells(6, i)
    Else
        .Cells(7, i + 10) = .Cells(6, i)
    End If
Next
End With
End Sub

Also make sure headers for Table and criteria match exactly. Had bit of issue with some of the column but fixed after copying and pasting over.

Working fine after above code used to move over criteria.
 
HI Chihiro,

I your Macros into a new Module and assigned a button. Before using a filter button, i am clicking on a MoveCriteria button and filter is working.
I do not know why it is not working without your Macros... As I wrote in my initial posting - I am using exactly the same approach as it is shown in :
http://www.onlinepclearning.com/exc...#Excel_Advanced_Filter_with_Multiple_Criteria
So, can I change formulas in Criteria range and macros will be working?
Thank you very much for your help.
 
HI Chihiro,

Unfortunately it does not work like It should. Your macro erases all formulas of Criteria from cells M7:S7. And macro "Clear" does not clear the Criteria in those cells...
Also, the Criteria with Dates selection in C6:D6 does not filter any data at all...

Problems...
 
Just run the code to move criteria again. It will wipe it clean if C6:I6 has no value in it. I believe your "Clear" code cleans value from C6:I6.

As for the dates. It sounds more like worksheet/cell setup issue than issue with Advanced Filter itself.

Make sure all date fields are not in text format and in accepted date format.
Chances are, original data has dates in text. In those instances ">" & "<" will return null value as it can't evaluate texts.
 
Chihiro,
Your Macros does not select the range of dates. C6 should be taking dates "From" and D6 "To". For example, January 2015- February 2015.
But your Macro is working backwards. If I put February 2015 in D6 and leave C6 empty, it filters January 2015. If I put any dates into both cells- the filter is not working... So, it is nothing to do with formatting...If you cannot change Macros
Can you look how it should be working ? http://www.onlinepclearning.com/exc...#Excel_Advanced_Filter_with_Multiple_Criteria

Thank you
 
Hmm? I copied criteria from the formula you had. Which had ">From Date" & "<To Date".

If you leave C6 blank and have date on D6 it will leave M7 blank and N7 with <"date".

So it's exclusive of those dates and not inclusive. If you want to change it to be inclusive change the Macro ">" with ">=" and "<" with "<=". Same goes for your formula.
 
Sorry, do not understand where to put :
">"with">=" and "<" with "<" in
Sub MoveCriteria()
Dim ws As Worksheet
Dim oRange As Range
Set ws = Sheets("FilterData")

With ws
For i = 3 To 9
If .Cells(6, i) = "" Then
.Cells(7, i + 10) = ""
ElseIf i = 3 Then
.Cells(7, i + 10) = ">" & .Cells(6, i)
ElseIf i = 4 Then
.Cells(7, i + 10) = "<" & .Cells(6, i)
Else
.Cells(7, i + 10) = .Cells(6, i)
End If
Next
End With
End Sub
 
Last edited:
Here you go. Also, if you are trying to filter for entire month. Make sure you have last day of the month (ex. 4/30/2015) in "to:" and first date in "From:", instead of just month-year. As Excel may display month-year but it's usually stored as first date of the month.
It's safer to just have it in yyyy-mm-dd format for the most part (or the format your language/region uses).

Code: 
Sub MoveCriteria()
Dim ws As Worksheet
Dim oRange As Range
Set ws = Sheets("FilterData")

With ws
For i = 3 To 9
If .Cells(6, i) = "" Then
.Cells(7, i + 10) = ""
ElseIf i = 3 Then
.Cells(7, i + 10) = ">=" & .Cells(6, i) 'Here
ElseIf i = 4 Then
.Cells(7, i + 10) = "<=" & .Cells(6, i) 'and here
Else
.Cells(7, i + 10) = .Cells(6, i)
End If
Next
End With
End Sub
 
Chihirio,

would you please explain me how to read your Macro= what does it mean each line and numbers? May be I could play myslef by changing the macro?

Thank you
 
See if this helps.

Code: 
Sub MoveCriteria()
Dim ws As Worksheet
Dim oRange As Range
Set ws = Sheets("FilterData") ' This sets ws variable to "FilterData" sheet

With ws 'With FilterData sheet
For i = 3 To 9 'For columns 3 to 9 (i.e. C to I)
If .Cells(6, i) = "" Then 'if row 6 is blank then
.Cells(7, i + 10) = "" 'set corresponding criteria cell (ex. M7) as blank
ElseIf i = 3 Then 'If i = 3 then (i.e. Cell C6)
.Cells(7, i + 10) = ">=" & .Cells(6, i) 'Set M7 to >="Date in C6"
ElseIf i = 4 Then 'If i = 4 then (i.e. Cell D6)
.Cells(7, i + 10) = "<=" & .Cells(6, i) 'Set N7 to <="Date in D6)
Else ' For all other cells in range E6:I6
.Cells(7, i + 10) = .Cells(6, i) ' Cell values for range O7:S7 equals corresponding cell in E6:I6
End If
Next
End With
End Sub
 
You must log in or register to reply here.
Back
Top