asked on

How to add a Message box in a Macro which uses Auto-filter in Excel

Hi Guys, I wish to amend this macro so it selects an Autofilter in an Excel spreadsheet and then selects
a date in a column defined by what I input into a message box. How do I do this? In the code below, I have recorded a Macro which filters in Column G for the rows prior to 07/09/2015, but I wish to input a different date every week into a Message box so it does the same. I enclose the spreadsheet with Macro.

Selection.AutoFilter
ActiveSheet.Range("$A$1:$AF$8461").AutoFilter Field:=7, Criteria1:= _
"<07/09/2015", Operator:=xlAnd
Commentary-Analysis.xlsm

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

[ fanpages ]

Hi,

Do you simply wish to enter a date into an Input Box, use the value in cell [C9] of the [Macros] worksheet, or automatically use a date based on whatever day (of the month/year) the button is clicked?

PS. I would also suggest using a format like [Dd-Mmm-YYYY] (i.e. 07-Sep-2015) to avoid confusion with the transposition of the day/month values; 09-Jul-2015, for example.

Justin

ASKER

Hi, I am trying to use this code to delete trades in between 07th Sep 2015 and 14th Sep 2015 using the Message box but its not working any ideas?

Sheets("All trades").Select
Rows("1:1").Select
Selection.AutoFilter
strdate = Application.InputBox("please delete all trades between which dates?")

ActiveSheet.Range("$A$1:$AF$111584").AutoFilter Field:=6, Criteria1:= _
">=" & strdate, Operator:=xlAnd
strdate1 = Application.InputBox("please delete all trades between which dates?")
ActiveSheet.Range("$A$1:$AF$111584").AutoFilter Field:=7, Criteria1:= _
"<=" & strdate1, Operator:=xlAnd