Justin
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:$A F$8461").A utoFilter Field:=7, Criteria1:= _
"<07/09/2015", Operator:=xlAnd
Commentary-Analysis.xlsm
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:$A
"<07/09/2015", Operator:=xlAnd
Commentary-Analysis.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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("plea se delete all trades between which dates?")
ActiveSheet.Range("$A$1:$A F$111584") .AutoFilte r Field:=6, Criteria1:= _
">=" & strdate, Operator:=xlAnd
strdate1 = Application.InputBox("plea se delete all trades between which dates?")
ActiveSheet.Range("$A$1:$A F$111584") .AutoFilte r Field:=7, Criteria1:= _
"<=" & strdate1, Operator:=xlAnd
Sheets("All trades").Select
Rows("1:1").Select
Selection.AutoFilter
strdate = Application.InputBox("plea
ActiveSheet.Range("$A$1:$A
">=" & strdate, Operator:=xlAnd
strdate1 = Application.InputBox("plea
ActiveSheet.Range("$A$1:$A
"<=" & strdate1, Operator:=xlAnd
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.