Excel VBA filter between dynamic date range

Closed
Report
Posts
1
Registration date
Thursday May 23, 2013
Status
Member
Last seen
May 23, 2013
-
Hi, I'm quite new to VBA and this has probably been asked before as well, so I'm sorry.
I am needing to filter out between two dates, but each day it will be different, so I want it as dynamic as possible.

I currently have

Dim firstRow As Integer
Dim lastRow As Integer
Dim sheetRange As String
Dim myDate As Date

firstRow = 2
myDate = Date
lastRow = Range("B" & Rows.Count).End(xlUp).Row
sheetRange = "A" & firstRow & ":U" & lastRow

ActiveSheet.Range(sheetRange).AutoFilter Field:=6, Operator:= _
xlFilterValues, Criteria1:=Array(2, myDate , myDate - 1, myDate - 2, _
myDate - 3, myDate - 4, myDate - 5)


There is most likely an easier way of doing this.

I have tried


ActiveSheet.Range(sheetRange).AutoFilter Field:=6, Criteria1:= _
">=" & myDate - 5, Operator:=xlAnd, Criteria2:="<=" & myDate

But no dice.


I know I could Dim a new Date for myDate - 5 and use that, but later on in the script I may only want myDate - 4, or myDate - 11. So I don't want to Dim a new Date for every one I need
.
Is this possible?

Thanks,