Create a macro to run autofilter based on contents of 2 cells
 

I have a list of dates in column A, starting in cell A3 upto cell A1002. I
have the autofilter in place for column A. I want to run a macro that will
filter these dates to show only the ones that are equal or greater than the
first date that is in cell A1, and also that are equal or less than the
second date in cell B1.
I can filter the information using the custom part of the autofilter, but
I'd rather be able to type the to and from dates in the two cells stated and
press a button to run the macro.

I hope that makes sense. Any help would be greatly appreciated.

Create a macro to run autofilter based on contents of 2 cells
 

I hope that your cell A3 contains some header like "Date".

Insert this code in a module. Instructions for how to insert code in a
regular module are here http://www.contextures.com/xlvba01.html#Top

Sub TryThis()

Range("A3").AutoFilter Field:=1, Criteria1:="=" & Range("A1").Value,
Operator:=xlAnd, Criteria2:="<=" & Range("B1").Value

End Sub

Now you can put a button into your sheet to run this code.
The easiest way is to do Edit Toolbars Forms. From there you can drag
and drop a button into your sheet (put it right at the top otherwise you
won't see it when the filter is applied). Excel kindly asks you to link it
to a macro, and you can just choose the TryThis macro.


--
Allllen


"Syndrome" wrote:

I have a list of dates in column A, starting in cell A3 upto cell A1002. I
have the autofilter in place for column A. I want to run a macro that will
filter these dates to show only the ones that are equal or greater than the
first date that is in cell A1, and also that are equal or less than the
second date in cell B1.
I can filter the information using the custom part of the autofilter, but
I'd rather be able to type the to and from dates in the two cells stated and
press a button to run the macro.

I hope that makes sense. Any help would be greatly appreciated.

Create a macro to run autofilter based on contents of 2 cells
 

Hi,

Thanks for this but I can't seem to get the macro to work. I've copied the
code to a new module within the spreadsheet. As my header is in cell A2 I
amended the macro accordingly.

When I run the macro I get the following message:-

Compile error
Syntax error

The first line of code below the sub heading is highlighted.

Hope this makes sense and any further help would be appreciated.

Thanks

"Allllen" wrote:

I hope that your cell A3 contains some header like "Date".

Insert this code in a module. Instructions for how to insert code in a
regular module are here http://www.contextures.com/xlvba01.html#Top

Sub TryThis()

Range("A3").AutoFilter Field:=1, Criteria1:="=" & Range("A1").Value,
Operator:=xlAnd, Criteria2:="<=" & Range("B1").Value

End Sub

Now you can put a button into your sheet to run this code.
The easiest way is to do Edit Toolbars Forms. From there you can drag
and drop a button into your sheet (put it right at the top otherwise you
won't see it when the filter is applied). Excel kindly asks you to link it
to a macro, and you can just choose the TryThis macro.


--
Allllen


"Syndrome" wrote:

I have a list of dates in column A, starting in cell A3 upto cell A1002. I
have the autofilter in place for column A. I want to run a macro that will
filter these dates to show only the ones that are equal or greater than the
first date that is in cell A1, and also that are equal or less than the
second date in cell B1.
I can filter the information using the custom part of the autofilter, but
I'd rather be able to type the to and from dates in the two cells stated and
press a button to run the macro.

I hope that makes sense. Any help would be greatly appreciated.

Create a macro to run autofilter based on contents of 2 cells
 

Further Update:

I tried putting the code all on one line rather than on two lines as posted
below. ( I pressed delete at the end of the first line of code). The Macro
then ran and filtered column A but but didn't show any filtered data, as
though the dates in cells A3 onwards were outside of the range of dates in
cells A1 & B1.

Thanks again

"Syndrome" wrote:

Hi,

Thanks for this but I can't seem to get the macro to work. I've copied the
code to a new module within the spreadsheet. As my header is in cell A2 I
amended the macro accordingly.

When I run the macro I get the following message:-

Compile error
Syntax error

The first line of code below the sub heading is highlighted.

Hope this makes sense and any further help would be appreciated.

Thanks

"Allllen" wrote:

I hope that your cell A3 contains some header like "Date".

Insert this code in a module. Instructions for how to insert code in a
regular module are here http://www.contextures.com/xlvba01.html#Top

Sub TryThis()

Range("A3").AutoFilter Field:=1, Criteria1:="=" & Range("A1").Value,
Operator:=xlAnd, Criteria2:="<=" & Range("B1").Value

End Sub

Now you can put a button into your sheet to run this code.
The easiest way is to do Edit Toolbars Forms. From there you can drag
and drop a button into your sheet (put it right at the top otherwise you
won't see it when the filter is applied). Excel kindly asks you to link it
to a macro, and you can just choose the TryThis macro.


--
Allllen


"Syndrome" wrote:

I have a list of dates in column A, starting in cell A3 upto cell A1002. I
have the autofilter in place for column A. I want to run a macro that will
filter these dates to show only the ones that are equal or greater than the
first date that is in cell A1, and also that are equal or less than the
second date in cell B1.
I can filter the information using the custom part of the autofilter, but
I'd rather be able to type the to and from dates in the two cells stated and
press a button to run the macro.

I hope that makes sense. Any help would be greatly appreciated.