Filter 'From' 'To' Dates in 1 Col
 

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

Filter 'From' 'To' Dates in 1 Col
 

you can execute step by step to find which step is wrong and what does
exactly each one...
Here are my ideas:
FromDate = Worksheets("Sheet1").AutoFilter.Range("D5:D11")

why "FromDate = ..." ? don't you already have fromdate set ?
what was the wanted purpose of this line ?
(forget the line)

Range("D4").Select
do not need to select it, forget it too

Selection.AutoFilter
-- this is not great because you cut it if already on !
With Worksheets("Sheet1")
If not .AutoFilterMode Then
.range("D4").autofilter
end if
'this one replace the old end if . Now you are sure it's on at this step
and if you think a filter can be somewhere else:
dim a$
a="$D$4"
If .AutoFilterMode Then 'it's already on
if .autofilter.range.address < a then ' but on the wrong range
.autofiltermode = off 'cut the old one off
.range(a).autofilter
end if
else ' it was off
.range(a).autofilter 'activate it if not
end if

With .AutoFilter.Filters(1)
If .On Then FromDate = .Criteria1
it's already on ! forget that test
and you wish the opposite: .criteria1 = fromdate
(I think you do not really take care of what you are doing)

Whatever, forget all the lines from this precedent one included until this
last one (you keep it):

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

which was the only usefull but is not correct:
Selection.AutoFilter field:=1, Criteria1:="<=" & FromDate,
Operator:=xlAnd _
, Criteria2:="<=" &ToDate

end with
end sub

first try this and remember to 1- exeute step by step to see what really
does each step
2- do not write ramdom code: read the visual basic help to know how works
the properties you use

Filter 'From' 'To' Dates in 1 Col
 

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

Filter 'From' 'To' Dates in 1 Col
 

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