Something wrong with multiple criteria filter macro

[Gliori]

Hello! I'm having trouble with this filter macro below. What I want to do is to filter everything except "jan" so I've written all the other months in the criteria. But for some reason it filters everything and the sheet becomes blank. Can anyone help me with this one?

Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array(feb, mar, apr, maj, jun, jul, aug, sep, okt, nov, dec, "="), Operator:=xlFilterValues

 

[KeepTrying]

Hi,

Try this code:

Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array("feb", "mar", "apr", "maj", "jun", "jul", "aug", "sep", "okt", "nov", "dec"), Operator:=xlFilterValues

Kind Regards,

 

[Gliori]

Hey, thank you for your reply. Unfortunately I still get the same result, it filters everything to row 500...
I use the Microsoft Office Excel version 2007 if that matters. I've read something about that you need to use advanced filter and not autofilter when filtering with multiple criterias. How does this work?

Thanks on beforehand!

 

[MARK858]

Are the months you are filtering on text or are they dates formatted to appear as months?

 

[Gliori]

I've formatted them to appear as regular text, and not dates.


Here's my whole VBA code. As you can see I want my macro the filter all months after the one you choose in the input box. But I can't get it to work properly... Can you find any errors in my code?

Sub Ta_bort_senare_fakturor()
   Dim Myrange As Range, Cell As Range
    Dim s As String
    Set Myrange = Range("L4:L500")
    For Each Cell In Myrange
        s = Cell.Text
        Cell.NumberFormat = "@"
        Cell.Value = s
    Next Cell
x = InputBox("To what month do you want to filter?" & vbCr _
& "1=Jan" & vbCr _
& "2=Feb" & vbCr _
& "3=Mar" & vbCr _
& "4=Apr" & vbCr _
& "5=Maj" & vbCr _
& "6=Jun" & vbCr _
& "7=Jul" & vbCr _
& "8=Aug" & vbCr _
& "9=Sep" & vbCr _
& "10=Okt" & vbCr _
& "11=Nov" & vbCr _
& "12=Dec", , "")


    While UR < 1 Or UR > 12
        UserResp = InputBox("Choose the number which represents the month you want to filter till.")
        UR = Val(UserResp)
    Wend
    Select Case UR
        Case 1
           Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array(feb, mar, apr, maj, jun, jul, aug, sep, okt, nov, dec, "="), Operator:=xlFilterValues
     ActiveWindow.SmallScroll Down:=-54
        Case 2
               Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array(mar, apr, maj, jun, jul, aug, sep, okt, nov, dec, "="), Operator:=xlFilterValues
     ActiveWindow.SmallScroll Down:=-54
        Case 3
               Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array(apr, maj, jun, jul, aug, sep, okt, nov, dec, "="), Operator:=xlFilterValues
     ActiveWindow.SmallScroll Down:=-54
        Case 4
               Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array(maj, jun, jul, aug, sep, okt, nov, dec, "="), Operator:=xlFilterValues
     ActiveWindow.SmallScroll Down:=-54
        Case 5
               Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array(jun, jul, aug, sep, okt, nov, dec, "="), Operator:=xlFilterValues
     ActiveWindow.SmallScroll Down:=-54
        Case 6
               Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array(jul, aug, sep, okt, nov, dec, "="), Operator:=xlFilterValues
     ActiveWindow.SmallScroll Down:=-54
        Case 7
               Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array(aug, sep, okt, nov, dec, "="), Operator:=xlFilterValues
     ActiveWindow.SmallScroll Down:=-54
        Case 8
              Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array(sep, okt, nov, dec, "="), Operator:=xlFilterValues
     ActiveWindow.SmallScroll Down:=-54
        Case 9
               Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array(okt, nov, dec, "="), Operator:=xlFilterValues
     ActiveWindow.SmallScroll Down:=-54
        Case 10
               Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array(nov, dec, "="), Operator:=xlFilterValues
     ActiveWindow.SmallScroll Down:=-54
        Case 11
               Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:=Array(dec, "="), Operator:=xlFilterValues
     ActiveWindow.SmallScroll Down:=-54
    End Select
End Sub

EDIT: If I just write one or two words in the criteria it all works fine. I know that two criterias is the limit for AutoFilter therefore I wonder if I need to use a different method? Or I have just done something wrong?

 

[MARK858]

See if the code below helps (please note it only covers for the current year)

Sub Macro2()
    Dim x As Long, y As Long, RESP1 As String, RESP2 As String

    RESP1 = InputBox("Choose the number which represents the month you want to filter from.")
    RESP2 = InputBox("Choose the number which represents the month you want to filter to.")

    x = DateSerial(Year(Date), Month(RESP1) + 1, 1)
    y = DateSerial(Year(Date), RESP2 + 1, 0)

    Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:= _
                                    ">=" & x, Operator:=xlAnd, Criteria2:="<=" & y
End Sub

 

[Gliori]

Thank you for the answer! Is there something I need to change to customize the code to my worksheet? I'm thinking about this section:
x = DateSerial(Year(Date), Month(RESP1) + 1, 1)
y = DateSerial(Year(Date), RESP2 + 1, 0)

 

[MARK858]

I haven't a clue what you are asking, run the code as is and then ask a better question with an explanation of what needs changing or what isn't happening.

The code filters based on you having dates in column L (your cells are still dates no matter how you format them).
Run the code, enter your start month number in the first input box and your end month number in the second inputbox.

Leave the section you mentioned alone at present.

 

[MARK858]

Actually I can see an issue, replace the code with...

Sub Macro2()
    Dim x As Long, y As Long, RESP1 As Integer, RESP2 As Integer

    RESP1 = InputBox("Choose the number which represents the month you want to filter from.")
    RESP2 = InputBox("Choose the number which represents the month you want to filter to.")
   
    x = DateSerial(Year(Date), RESP1 + 1, 1)
    y = DateSerial(Year(Date), RESP2 + 1, 0)

    Range("$A$1:$R$500").AutoFilter Field:=12, Criteria1:= _
                                    ">=" & x, Operator:=xlAnd, Criteria2:="<=" & y
End Sub

Basically I put RESP1 inside MONTH() which is bad... very bad

 

[Gliori]

Wow, nice thanks, it almost works perfectly fine! However when I for example type 2 in the first box and 5 in the second, meaning I want to keep the months 2-5, (february, mars, april and may) it filters month 2 (february) and leaves the three following months. How do I change the code to perform this?


Edit:

I changed this
x = DateSerial(Year(Date), RESP1 + 1, 1)

To this
x = DateSerial(Year(Date), RESP1 + 0, 1)

And now it works just like I want!

A very BIG BIG thank you !!! This was exactly what I was looking for, and this code was much shorter than the one I used from the beginning too