About Us

Dianne Butterworth[_2_]

You're welcome.

--
Dianne Butterworth

John Childs wrote:
Diane, it worked!! A brilliant and elegant solution. Thanks, John

"Dianne Butterworth" wrote:

Sub FilterDates()

Dim FromDate As Date
Dim ToDate As Date
Dim rngToFilter As Range

With Worksheets("Sheet1")
FromDate = .Range("F2").Value2
ToDate = .Range("F3").Value2
Set rngToFilter = .Range("D4:D11")
End With

rngToFilter.AutoFilter _
Field:=1, _
Criteria1:="=" & FromDate, _
Operator:=xlAnd, _
Criteria2:="<=" & ToDate

End Sub

--
Dianne Butterworth

John Childs wrote:
I have one column of dates, say, in the range D5:D11. My header is
at D4. In F2 I have a variable "FromDate" and in F3 a variable
"ToDate". I would like to be able to enter a 'From' and 'To' date
in cells F2 & F3 respectively. Then have a VBA program grab those
2 dates and filter this single column of dates by saying date range
is greater than or equal to F2 AND less than or equal to F3.

Below is the VBA code I have attempted with no success. You help
would be appreciated.

Thanks,
John

Sub FilterDates()

Dim FromDate As Date
Dim ToDate As Date

FromDate = Range ("F2") 'Location on Worksheet where the FromDate
will be entered.

ToDate = Range ("F3") 'Location on Worksheet where the ToDate
will be entered.

FromDate = Worksheets("Sheet1").AutoFilter.Range("D5:D11")

Range("D4").Select 'This is the header cell above the single
column of dates.

Selection.AutoFilter
With Worksheets("Sheet1")
If .AutoFilterMode Then
With .AutoFilter.Filters(1)
If .On Then FromDate = .Criteria1
End With
End If
End With

'Selection.AutoFilter
With Worksheets("Sheet1")
If .AutoFilterMode Then
With .AutoFilter.Filters(1)
If .On Then ToDate = .Criteria2
End With
End If
End With

Selection.AutoFilter field:=1, Criteria1:="=FromDate",
Operator:=xlAnd _ , Criteria2:="<=ToDate"
End Sub

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Filter on Dates	suek	Excel Discussion (Misc queries)	4	August 11th 08 04:21 PM
Filter Dates?	Ken	Excel Discussion (Misc queries)	4	February 20th 07 09:27 PM
How to filter out all the partial dates from a big list of dates?	Tiwarr	Excel Discussion (Misc queries)	1	September 14th 06 05:35 PM
Filter dates by the day	Chris AM	Excel Discussion (Misc queries)	2	September 12th 06 05:16 PM
how to filter dates?	abigail	New Users to Excel	2	August 18th 05 12:45 AM

Menu

About Us