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

 

[Gliori]

You sir, are a genius!!!

Now it looks like it's working spot-on! Can't find any problems with this code!

Wow, I'm so happy now! I truly appreciate your help a lot, thank you!

 

[Gliori]

Wow I can't believe it. Noticed another thing that needs to be changed. The latest code you posted also deletes rows that are from 2014 in the "A" column, keeping only those who are from 2015 and between the selected months. I need to keep the rows who are from both 2014-15 between the selected months.


This code did that, but as I said before it also filtered the sum formulas and titles.

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 + 0, 1)
    y = DateSerial(Year(Date), RESP2 + 1, 0)


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

Is this possible?

Best regards!

 

[MARK858]

Told you that right back in post #6

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

This code did that

No it didn't

Will look at it at some time but makes it more complicated and don't have the time at the moment.

Of course if anyone jumps in...

 

[MARK858]

Or easily if you are ok to use an extra column?

 

[Gliori]

Yes I geuss using an extra column would be OK. Depends on where.

No it didn't

Actually it does. I'm using the code myself right now. It seems to only look at one column, or something.

 

[MARK858]

The code in post #22 filters on column L (as do all the other codes) by the current year which is the Year(Date) part. Date is the VBA version of =Today() so it says Year(18/6/2015) so it filters on any the cells in column L that are dated in this calendar yearonly.

One more question in Blad1 column D ("Otefal") is hidden. Should that column appear in MyTest?

 

[Gliori]

Before
After

As you can see using this

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 + 0, 1)
    y = DateSerial(Year(Date), RESP2 + 1, 0)


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

Kept the ones from 2014, and not only 2015.

One more question in Blad1 column D ("Otefal") is hidden. Should that column appear in MyTest?

Oh didn't even know it was there. I think that's old, so keep it hidden.

 

[MARK858]

I can't see because your image doesn't show the underlying years in column A or L, but column A is irrelevant as the code doesn't use it, format column L to show the year and you will find they all are in year 2015.

Change Blad1 so you have a cell in column A with a 2015 date and a cell on the same row in column L with a 2014 or 2016 date and see if the line appears in MyTest

Oh didn't even know it was there. I think that's old, so keep it hidden.

The question isn't do you want it hidden, it is do you want it there at all? it is currently removed as the code copies the visible cells only.

 

[Gliori]

Oh right, that's true. But they are sorted by date in the A column, so the ones with nov and dec there are from 2014. The rest from 2015.

The new code somhow removes the ones that have 2014 in them in the A column, or I don't know what it is.. I'll show you as soon as I get access to a computer.

And yes that column with otefal is useless, so it can be removed.

 

[MARK858]

And yes that column with otefal is useless, so it can be removed.

Try

Sub FiltTest3ish()
    Dim x As Long, y As Long, RESP1 As String, RESP2 As String, Shtx As Worksheet, LstRw As Long
    Dim LstRw2 As Long, icol As Long


    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.")

    Application.ScreenUpdating = False

    Set Shtx = Blad1

    With Shtx.Range("O4:O" & Shtx.Range("C" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=MONTH(RC[-3])"
        .Value = .Value
        With .Offset(, 1)
            .FormulaR1C1 = "=YEAR(RC[-15])"
            .Value = .Value
        End With
    End With

    x = RESP1
    y = RESP2 - 1


    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MyTest"

    LstRw = Shtx.Range("C" & Rows.Count).End(xlUp).Row

    With Shtx.Range("A3:R" & LstRw)
        .AutoFilter Field:=15, Criteria1:= _
                    ">=" & x, Operator:=xlAnd, Criteria2:="<=" & y
        .AutoFilter Field:=16, Criteria1:="=2014", _
                    Operator:=xlOr, Criteria2:="=2015"
        On Error Resume Next

        .Offset(-2).Resize(.Rows.Count + 3).SpecialCells(xlCellTypeVisible).Copy

        With Sheets("MyTest").Range("C" & Rows.Count).End(xlUp).Offset(1, -2)
            .PasteSpecial xlPasteColumnWidths
            .PasteSpecial
        End With

        On Error GoTo 0
        .AutoFilter
    End With

    With Sheets("MyTest")
        LstRw2 = .Cells(Rows.Count, "C").End(xlUp).Row
        For icol = 6 To 9
            .Cells(LstRw2 + 3, icol).Formula = "=SUM(" & .Range(.Cells(5, icol), .Cells(LstRw2, icol)).Address & ")"
        Next
        .Cells(LstRw2 + 3, 13).Formula = "=SUM(" & .Range(.Cells(5, 13), .Cells(LstRw2, 13)).Address & ")"
        .Columns("N:O").Delete
    End With
    Blad1.Columns("O:P").Delete

    Application.ScreenUpdating = True
End Sub