none
Autofilter To Filter Using Current Month As Criteria RRS feed

  • Question

  • I have a macro below that filters out certain data; I would like to add an additional filter based on the criteria for the current month. here is the logic: in addition to the other filters already in the macro only show data if column #35 = current month.

    Can someone help me with this?

    Code:

    Sub Pipeline_ShowNetRegs()
    '
    ActiveSheet.Unprotect
    If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
      ActiveSheet.ShowAllData
    End If
    ActiveSheet.Range("$a$7:$AQ$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
    ActiveSheet.Range("$A$7:$AQ$1000").AutoFilter Field:=8, Criteria1:="<>Post-Closing"
    ActiveSheet.Range("$A$7:$AQ$1000").AutoFilter Field:=7, Criteria1:="<>DECL", _
            Operator:=xlAnd, Criteria2:="<>WITH"
    ActiveSheet.Protect AllowFiltering:=True
    End Sub


    MEC


    • Edited by mecerrato Monday, August 1, 2016 4:29 PM
    Monday, August 1, 2016 4:28 PM

Answers

  • Hi MEC,

    >> but the code did not work, it filtered everything out.

    What do you mean filtered everything out? Did it show all rows or there is no rows show up after you run this code? What is the value you enter for date before you format? I assume it is related with your data and multiple autofilters. It would be helpful if you could share us your demo file.

    I suggest you comment out other filters, and only use below filter to see whether it is related with other filters.

    ActiveSheet.Range("E1:F15").AutoFilter field:=1, Operator:=xlFilterValues, Criteria1:=Array("="), Criteria2:=Array(1, Now())

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, August 3, 2016 2:21 AM

All replies

  • Hi MEC,

    >> in addition to the other filters already in the macro only show data if column #35 = current month.

    What is your value for column #35? If it is “8/2/2016”, you could try below code:

        ActiveSheet.Range("$a$7:$AQ$1000").AutoFilter Field:=35, Operator:= _
            xlFilterValues, Criteria2:=Array(1, Now())

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, August 2, 2016 2:46 AM
  • Edward, column #35 is a short date format and DOES show as "8/2/2016" but the code did not work, it filtered everything out. I thought I should mention that there are many rows that do not have any data in column #35.<o:p></o:p>

    If column #35 is blank or has a date from the current month I want to show the data

    Option 1 (using a helper column, column 48 contains =IF(AF7="","",MONTH(AF7)) which gives a numeric value of the month but this doesn’t work I get a Run-time error 1004 "Autofilter method of range class failed" and I don’t think it will take into account if column 48 is blank):
    ActiveSheet.Range("$A$7:$AQ$1000").AutoFilter Field:=48, Criteria1:=Month(Date)
    
    Option 2 (this was found on the web, this shows anything with a date of the current month but I need to modify it to show data if column 35 is blank or has a date in this month):
    ActiveSheet.Range("$A$7:$AQ$1000").AutoFilter Field:=35, _
               Criteria1:=">=" & DateSerial(Year(Date), Month(Date), 1), _
               Criteria2:="<=" & DateSerial(Year(Date), Month(Date) + 1, 1) - 1
    

    <o:p></o:p>


    MEC

    Tuesday, August 2, 2016 10:29 AM
  • Hi MEC,

    >> but the code did not work, it filtered everything out.

    What do you mean filtered everything out? Did it show all rows or there is no rows show up after you run this code? What is the value you enter for date before you format? I assume it is related with your data and multiple autofilters. It would be helpful if you could share us your demo file.

    I suggest you comment out other filters, and only use below filter to see whether it is related with other filters.

    ActiveSheet.Range("E1:F15").AutoFilter field:=1, Operator:=xlFilterValues, Criteria1:=Array("="), Criteria2:=Array(1, Now())

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, August 3, 2016 2:21 AM