code for dynamically filtering date range

Question

I'm using Excel VBA to code a weekly report. I want to automate the whole process but am hitting a block at the date range filter. I've managed to automate the filter, but I would have to update the weekly date range each time I run the report. Code below:

ActiveSheet.Range("$A$1:$AD$7520").AutoFilter _
Field:=2, _
Criteria1:=">=11/16/2019", _
Operator:=xlAnd, _
Criteria2:="<=11/22/2019"

Is there anyway to code these dates such that every Monday the dates automatically update? For example a code that does something similar to the excel function of Today()-9 for the Start Date and Today()-3 for the End Date.

I've tried the below but the filter just unselects all the dates:

Dim StartDate As String
Dim EndDate As String
'
StartDate = Date - 9
EndDate = Date - 3
'
ActiveSheet.Range("$A$1:$AD$7520").AutoFilter Field:=2, Criteria1:=">=StartDate", Operator:=xlAnd, 
Criteria2:="<=EndDate"

If you're using variables StartDate and EndDate, they shouldn't be within the quotes; concatenate with &. — BigBen
Just a note, I would suggest not to use string data type, but Long instead if you plan to share your file. AutoFilter and dates are not best friends amongst differnt local settings. — JvdV

jsteurer jsteurer · Accepted Answer · 2019-11-25T20:08:41

Give below a shot.

make sure to update the format of how the dates appear if they do not appear as mm/dd/yyyy

Sub test()


Dim StartDate As String
Dim EndDate As String
Dim lrow As Long

lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'
StartDate = Format(Date - 9, "MM/DD/YYYY")
EndDate = Format(Date - 3, "MM/DD/YYYY")
'
ActiveSheet.Range("$A$1:$AD" & lrow).AutoFilter Field:=2, Criteria1:=">=" & StartDate & "", Operator:=xlAnd, Criteria2:="<=" & EndDate & ""


End Sub

code for dynamically filtering date range

1 Answers