Code to allow user to enter criteria for autofilter

  • Thread starter Thread starter Ron McCormick
  • Start date Start date
R

Ron McCormick

I would like to write a procedure that would allow a user
to enter dates ie one greater than and the other less than
in a programmed autofilter procedure.

Essentially I would want the user to be able to insert
their own chosen dates in place of the dates that appear
in the undernoted code without them seeing the code.

Selection.AutoFilter Field:=3, Criteria1:=">31/10/2002",
Operator:=xlAnd, Criteria2:="<=31/10/2003"

TIA
Ron
 
Ron, how about something like this

Sub test()
Dim strA As String
Dim strB As String
strA = Application.InputBox("Start Date")
strB = Application.InputBox("End Date")

Selection.AutoFilter Field:=3, Criteria1:=">" & strA, _
Operator:=xlAnd, Criteria2:="<=" & strB
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Another option is to create dropdown lists of the dates on the worksheet
using data validation:

http://www.contextures.com/xlDataVal01.html

Create a dropdown for start date, and one for end date, then format
these dates the same as the dates in column C.

The user selects a start and end date from the dropdown lists, then
runs the AutoFilter macro:

'==================================
Selection.AutoFilter Field:=3, Criteria1:=">=" & Range("K1").Value, _
Operator:=xlAnd, Criteria2:="<=" & Range("L1").Value
'==================================
 
Thanks Debra & Paul,

Both responses have been helpful, but I have one further
problem. Whenever I run the macro the whole of the list
is hidden. If I then go to Data/Autofilter/Custom and
click OK the filtered list appears. Why does it not
appear automatically?

Thanks again
Ron
 
The problem may be your regional settings. Try formatting the date as a
number:

'==================================
Selection.AutoFilter Field:=3, Criteria1:=">=" &
Str(CDbl(Range("K1").Value)), _
Operator:=xlAnd, Criteria2:="<=" & Str(CDbl(Range("L1").Value))
'==================================
 
Back
Top