Forum Discussion

RonnieGK's avatar
RonnieGK
Copper Contributor
Dec 20, 2022

Problems with Multiple Filter in Macro

Hi, 

 

I am facing issues when filtering for multiple things in my Macro.

I am filtering on three different criteria's but when clicking the button I have created, instead of filtering all lines go away.  I have to manually go into every "custom filter" and press ok. After pressing the last of the three, it does the actual filtering.

 

The code I have used is as follows : 

 

Sub Sorter_Dato()
'
' Sorter_Dato Macro
'

'
ActiveSheet.Range("$A$13:$AA$2758").AutoFilter Field:=26, Criteria1:="<=" & Date + 7, Operator:=xlOr, Criteria2:="unconfirmed"
ActiveSheet.Range("$A$13:$AA$2758").AutoFilter Field:=27, Criteria1:=">=" & DateSerial(2022, 1, 1), Operator:=xlAnd, Criteria2:="<=" & Date - 5
ActiveSheet.Range("$A$12:$AA$2758").AutoFilter Field:=25, Criteria1:= _
"<=" & DateSerial(2022, 12, 30)

End Sub

 

Could anyone point out my error?

Thanks.

 

 

excel
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 20, 2022

    RonnieGK 

    AutoFilter does not sort data but filters them, i.e. it hides all rows that don't meet the specified criteria.

    Try this:

    Sub Sorter_Dato()
    Range("A13:AA2758").Sort _
        Key1:=Range("Y13"), _
        Key2:=Range("AA13"), _
        Key3:=Range("Z13"), _
        Header:=xlYes
End Sub
    • RonnieGK's avatar
      RonnieGK
      Copper Contributor
      Dec 20, 2022
      Hi, I was meaning filtering and writing sorting.

      I want to filter for three different things as described in the code and my post.
      But after running macro, all lines go away not only according to the filter criterias.

      I then have to go to text Filters -> Custom Filter and press "OK", on all three columns I want to filter. Then it shows the content I need.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Dec 20, 2022

        RonnieGK 


        Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Resources