Advanced filter using a range of date

[monmon]

Hi all, I need help here.

I've been trying this for hours and can't find a solution to this.

What I have is a set of data and I'm filtering to find out all data between a range of dates.

the Criteria would be anything that is >=today() and <=today-8.

this is what i have done and it does not work at all. please help

Sheets(2).Select

Dim dDate As Date
Dim lDate As Date

Range("N2").Value = "=today()"
Range("o2").Value = "=today()-8"

dDate = Range("N2")
lDate = Range("O2")

Range("A1").AutoFilter
Range("A1").AutoFilter Field:=10, Criteria1:=">=" & lDate, Operator:=xlAnd, Criteria2:="<=" & dDate

 

[Trevor G]

Change the Dim dDate As Date to
Dim dDate As Long

Same for the other one.

 

[dmt32]

Hi all, I need help here.

I've been trying this for hours and can't find a solution to this.

What I have is a set of data and I'm filtering to find out all data between a range of dates.

the Criteria would be anything that is >=today() and <=today-8.

this is what i have done and it does not work at all. please help

Sheets(2).Select

Dim dDate As Date
Dim lDate As Date

Range("N2").Value = "=today()"
Range("o2").Value = "=today()-8"

dDate = Range("N2")
lDate = Range("O2")

Range("A1").AutoFilter
Range("A1").AutoFilter Field:=10, Criteria1:=">=" & lDate, Operator:=xlAnd, Criteria2:="<=" & dDate

Try changing your Date variables as Long & then pass them through DateSerial & see if that helps.

Something like this:

Sub FilterDates()
    Dim dDate As Long
    Dim lDate As Long
    With Sheets(2)
        .Range("N2").Formula = "=today()"
        .Range("O2").Formula = "=today() - 8"
        dDate = DateSerial(Year(.Range("N2").Value), Month(.Range("N2").Value), Day(.Range("N2").Value))
        lDate = DateSerial(Year(.Range("O2").Value), Month(.Range("O2").Value), Day(.Range("O2").Value))
        .Range("A1").AutoFilter
        .Range("A1").CurrentRegion.AutoFilter Field:=.Range("J1").Column, _
                                              Criteria1:=">=" & lDate _
                                            , Operator:=xlAnd, _
                                              Criteria2:="<=" & dDate
    End With
End Sub

Dave