Macro to set a range date

[neo_cd]

Hello,

I have a spreadsheet with 143 rows containing a "Target Date" column.

I would like to create a macro that will be able to set a range and filter the dates. As an example, I would like to filter the rows containing a date from May 31, 2018 to July 31, 2018.

What would be the best option to achieve that? To have an input message box and type the dates or put the requested dates in 2 differents cells and mention them in the macro?

Let me know if my request is not clear enough and I will provide more details.

Thanks!

 

[Jerry Sullivan]

Welcome to MrExcel,

That's largely a matter of personal preference. I personally would rather use an interface that has the two cells (Start date, end date), and a button that says something like "Update Filter".

 

[neo_cd]

Thanks Jerry!

Finally I found the following solution which is perfect for my needs :

Dim W1Startdate As Date, W1Enddate As Date

Selection.NumberFormat = "mm/dd/yyyy"

W1Startdate = ActiveSheet.Range("G1")
W1Enddate = ActiveSheet.Range("I1")

ActiveSheet.Range("A4:AH143").AutoFilter Field:=9, Criteria1:=">=" & W1Startdate, Criteria2:="<=" & W1Enddate
ActiveSheet.Range("A4:AH143").AutoFilter Field:=9, Criteria1:=">=" & W1Startdate, Criteria2:="<=" & W1Enddate