I want to filter a column of data by a date range or month and allow the
user to enter a beginning and end date (or a month number). U using the
record macro button I have created the following macro.

Sub OPI_FilterbyTargetDate()
'
' OPI_FilterbyTargetDate Macro
' Macro recorded 05/04/2007 by Dylan D

Selection.AutoFilter Field:=22, Criteria1:="=30/04/2007",
Operator:=xlAnd
End Sub

I want to bring up the custom filter dialog box to allow the user to enter a
date range.
How do I remove the text "Criteria1:="=30/04/2007"" and allow the user to
enter their own date range e.g., greater than 01-01-04 and less than
30-01-04.

Is there an easier way which will allow the users to enter the month number?

D Dawson

I think you're going to have to design your own input form using a userform
in the VBE. That will be some work as you'll have to parse/validate user
entries and then feed the dates into your macro. Personally I'd try to
train the users to use the built-in tools directly, if at all possible.

--
Jim
"dd" <dd.dd> wrote in message
news:%(E-Mail Removed)...
|I want to filter a column of data by a date range or month and allow the
| user to enter a beginning and end date (or a month number). U using the
| record macro button I have created the following macro.
|
| Sub OPI_FilterbyTargetDate()
| '
| ' OPI_FilterbyTargetDate Macro
| ' Macro recorded 05/04/2007 by Dylan D
|
| Selection.AutoFilter Field:=22, Criteria1:="=30/04/2007",
| Operator:=xlAnd
| End Sub
|
| I want to bring up the custom filter dialog box to allow the user to enter
a
| date range.
| How do I remove the text "Criteria1:="=30/04/2007"" and allow the user to
| enter their own date range e.g., greater than 01-01-04 and less than
| 30-01-04.
|
| Is there an easier way which will allow the users to enter the month
number?
|
| D Dawson
|
|
|

Hi DD,

Another way would be to enter your minimum date in a cell, say AA1,
and your max date in a second cell, AB1. Then change your macro statement
to:

Selection.AutoFilter Field:=1, Criteria1:=">=" &
CDate(Range("AA1")), _
Operator:=xlAnd, Criteria2:="<=" & CDate(Range("AB1"))

CHORDially,
Art Farrell

"dd" <dd.dd> wrote in message
news:%(E-Mail Removed)...
> I want to filter a column of data by a date range or month and allow the
> user to enter a beginning and end date (or a month number). U using the
> record macro button I have created the following macro.
>
> Sub OPI_FilterbyTargetDate()
> '
> ' OPI_FilterbyTargetDate Macro
> ' Macro recorded 05/04/2007 by Dylan D
>
> Selection.AutoFilter Field:=22, Criteria1:="=30/04/2007",
> Operator:=xlAnd
> End Sub
>
> I want to bring up the custom filter dialog box to allow the user to enter
a
> date range.
> How do I remove the text "Criteria1:="=30/04/2007"" and allow the user to
> enter their own date range e.g., greater than 01-01-04 and less than
> 30-01-04.
>
> Is there an easier way which will allow the users to enter the month
number?
>
> D Dawson
>
>
>