Excel Macro Script Help for Date Filter

Question

I am trying to find a MACRO script that I can use to automate some filters within a shared Excel document and need a Dynamic Date Filter that adapts to the current day.

What I am looking to do is Filter everything in the column up to the Current Day and including the current day as well. I also need it to adapt daily so it looks each time the day changes when I run the Macro. Also, the column sometimes does not have the current day in it so if that's the case then I need it just to filter everything before the present day as far as it can go or however many dates there are.

Is this something that is possible?

Answer 1

Be more specific with your filter range, like this, if your header for column L is in L3 (and you have no merged cells in L3 or below, or , if you do, then they are merged with L as the left-most cell, and the merging is consistent):

Sub YTDFilter2()

With ActiveSheet 

 .Range(.Range("L3"), .Range("L3").End(xlDown)).AutoFilter Field:=1, Criteria1:= \_ 

    xlFilterYearToDate, Operator:=xlFilterDynamic 

End With 

End Sub

Answer 2

Yea there are a lot of merged cells within the worksheet, It is a rather large worksheet and the merged cells are a necessary evil at this point. Is there any way around this with the merged cells?

Answer 3

If you have any merged cells on your worksheet, the code will fail. Merged cells are bad ;)

If that is not the issue, share a cleaned-up copy of your workbook by posting a link here to a file sharing site.

Answer 4

When changing the columns to the correct input, the macro seems to put the filter on the first column within the spreadsheet. The field I change I put in L7:L10000 and it still filters on Column A with a custom filter for some reason.

If it is relevant, the date format is as follows: Day DD-MM-YY, for ex. Wed 05-Oct-22

Answer 5

Just replace the C:C with the column letter that holds the date values, like E:E

Sub YTDFilter()

ActiveSheet.Range("C:C").AutoFilter Field:=1, Criteria1:= \_ 

    xlFilterYearToDate, Operator:=xlFilterDynamic 

End Sub

If your headers are in the first row, then this will work fine - if not, then change C:C to C<Row of headers>:C<large number> like C5:C1000 or E5:E1000