# Advanced filter using a range of date

#### monmon

##### Board Regular
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.

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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Trevor G

##### Well-known Member
Change the Dim dDate As Date to
Dim dDate As Long

Same for the other one.

#### dmt32

##### Well-known Member
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.

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:

Code:
``````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

Replies
2
Views
225
Replies
3
Views
150
Replies
4
Views
295
Replies
1
Views
89
Replies
2
Views
176

1,195,642
Messages
6,010,885
Members
441,571
Latest member
stolenweasel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back