Filtering Dates in a table with input boxes

[R Tanner]

Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...


The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...


MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))



ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)

 

[TomPl]

Try this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:=">=MYFILTER1,3", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

Tom

 

[Rick Rothstein \(MVP - VB\)]

If you need the date of the last day in the month of the date the user
inputs, give this a try...

MYFILTER1 = InputBox("Please enter the first day of the period " & _
"you would like to generate this report for:", "First Date")
If IsDate(MYFILTER1) Then
MYFILTER1 = DateSerial(Year(MYFILTER1), Month(MYFILTER1) + 1, 0)
End If

Rick

 

[R Tanner]

If you need the date of the last day in the month of the date the user
inputs, give this a try...

MYFILTER1 = InputBox("Please enter the first day of the period " & _
"you would like to generate this report for:", "First Date")
If IsDate(MYFILTER1) Then
MYFILTER1 = DateSerial(Year(MYFILTER1), Month(MYFILTER1) + 1, 0)
End If

Rick

What I am really trying to do is get the first and last date of the
period for the report from the user. Not necessarily just the last
date...I'm not sure if I made that clear or not...

Thanks for your input...

 

[R Tanner]

I tried your idea with the filter 1 and 2 and the criteria but it did
not work...it didn't unselected everything in the table rather than
just the specified dates...

 

[TomPl]

I erroneously included ,3. This should work better.

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:=">=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

 

[TomPl]

The code should filter to show everything between the two dates inclusive.
Can't imagine why it doesn't work.

 

[R Tanner]

I erroneously included ,3. This should work better.

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:=">=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

it still didn't work...I have my filters declared as dates...That is
how is should be right?

 

[R Tanner]

I erroneously included ,3. This should work better.

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:=">=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

it still didn't work...I have my filters declared as dates...That is
how is should be right?

 

[R Tanner]

I erroneously included ,3. This should work better.

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:=">=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

it still didn't work...I have my filters declared as dates...That is
how is should be right?

 

[R Tanner]

I erroneously included ,3. This should work better.

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:=">=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

it still didn't work...I have my filters declared as dates...That is
how is should be right?

 

[TomPl]

Sorry,

Replace this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:=">=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

With this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:=">=" & MYFILTER1, Operator:=xlAnd, _
Criteria2:="<=" & MYFILTER2

It was hard to test because I don't have your worksheet.
Remember to enter the dates in a format that excel recognises as a date.
MYFILTER1 & MYFILTER2 should be declared as dates.

Hope this works.

 

[R Tanner]

Sorry,

Replace this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:=">=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

With this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:=">=" & MYFILTER1, Operator:=xlAnd, _
Criteria2:="<=" & MYFILTER2

It was hard to test because I don't have your worksheet.
Remember to enter the dates in a format that excel recognises as a date.
MYFILTER1 & MYFILTER2 should be declared as dates.

Hope this works.

Thank you Tom...you are awesome...this did work...

 

[Ron de Bruin]

Try EasyFilter
http://www.rondebruin.nl/easyfilter.htm