Date Filter Help

pkaskaggs

New Member
Joined
Jul 13, 2010
Messages
23
Hello, I recently got help here writing a macro to help clean up a report. It used this code:

Option Explicit
Sub ReordData()
' hiker95, 07/14/2010, http://www.mrexcel.com/forum/showthread.php?t=480930
Application.ScreenUpdating = False
With ActiveSheet
.AutoFilterMode = False
With .Range("Y1", .Range("Y" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="=*Shipped dt*", Operator:=xlOr, Criteria2:="=*Inv dt*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
'.AutoFilter Field:=1, Criteria1:="=*Est Comp dt*", Operator:=xlAnd
'On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
.Columns("Z").EntireColumn.Delete
.Columns("V:W").EntireColumn.Delete
.Columns("O:T").EntireColumn.Delete
.Columns("J:M").EntireColumn.Delete
.Columns("F").EntireColumn.Delete
.Columns("C:D").EntireColumn.Delete
.Columns("G").EntireColumn.Insert
End With
Dim LR As Long, LR2 As Long
LR = Worksheets("Dealer List").Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
With Worksheets("Sheet1").Range("G2:G" & LR2)
.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC6,'Dealer List'!R1C1:R" & LR & "C2,2,FALSE)),"""",VLOOKUP(RC6,'Dealer List'!R1C1:R" & LR & "C2,2,FALSE))"
End With
Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Application.ScreenUpdating = True
End Sub

To Edit a very long report down to this
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 128px"><COL style="WIDTH: 67px"><COL style="WIDTH: 97px"><COL style="WIDTH: 59px"><COL style="WIDTH: 98px"><COL style="WIDTH: 78px"><COL style="WIDTH: 273px"><COL style="WIDTH: 126px"><COL style="WIDTH: 150px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">123</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">JTDBU4EE1A9111212</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">1838</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2010/07/19</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Rev Comp dt</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">124</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">JTDBU4EE6AJ062491</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">1838</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2010/07/19</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Rev Comp dt</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">125</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">JTDBU4EE9AJ062520</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">1838</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2010/07/16</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Est Comp dt</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">126</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">JTDBU4EE9AJ062713</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">1838</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2010/07/27</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Rev Comp dt</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G123</TD><TD>=IF(ISNA(VLOOKUP($F123,'Dealer List'!$A$1:$B$4252,2,FALSE)),"",VLOOKUP($F123,'Dealer List'!$A$1:$B$4252,2,FALSE))</TD></TR><TR><TD>G124</TD><TD>=IF(ISNA(VLOOKUP($F124,'Dealer List'!$A$1:$B$4252,2,FALSE)),"",VLOOKUP($F124,'Dealer List'!$A$1:$B$4252,2,FALSE))</TD></TR><TR><TD>G125</TD><TD>=IF(ISNA(VLOOKUP($F125,'Dealer List'!$A$1:$B$4252,2,FALSE)),"",VLOOKUP($F125,'Dealer List'!$A$1:$B$4252,2,FALSE))</TD></TR><TR><TD>G126</TD><TD>=IF(ISNA(VLOOKUP($F126,'Dealer List'!$A$1:$B$4252,2,FALSE)),"",VLOOKUP($F126,'Dealer List'!$A$1:$B$4252,2,FALSE))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

What I would like to do now is insert code that will delete all dates previous to todays date, and all dates that are further than 7 days into the future.

So if today were 7/16/2010 it would delete row 126 for being out of the 7 day range.

I have found many examples of date filtering but I have yet to come across one that will help my situation specifically. The template is a blank page that will have the data pasted into it.

Thanks in advance for any help.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

pkaskaggs

New Member
Joined
Jul 13, 2010
Messages
23
I just found out the report will be changed. We are going to add a pivot table, so all I need to do is insert


=IF(J2>TODAY(),1,0)+IF(J2<TODAY()+7,1,0)< b>Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 60px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3815</TD><TD style="FONT-SIZE: 9pt; TEXT-ALIGN: right">2010/07/28</TD><TD style="FONT-SIZE: 9pt">Est VPC Arr</TD><TD style="FONT-SIZE: 9pt; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3816</TD><TD style="FONT-SIZE: 9pt; TEXT-ALIGN: right">2010/08/04</TD><TD style="FONT-SIZE: 9pt">Est VPC Arr</TD><TD style="FONT-SIZE: 9pt; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3817</TD><TD style="FONT-SIZE: 9pt; TEXT-ALIGN: right">2010/07/28</TD><TD style="FONT-SIZE: 9pt">Est VPC Arr</TD><TD style="FONT-SIZE: 9pt; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3818</TD><TD style="FONT-SIZE: 9pt; TEXT-ALIGN: right">2010/07/28</TD><TD style="FONT-SIZE: 9pt">Est VPC Arr</TD><TD style="FONT-SIZE: 9pt; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3819</TD><TD style="FONT-SIZE: 9pt; TEXT-ALIGN: right">2010/07/28</TD><TD style="FONT-SIZE: 9pt">Est VPC Arr</TD><TD style="FONT-SIZE: 9pt; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3820</TD><TD style="FONT-SIZE: 9pt; TEXT-ALIGN: right">2010/07/28</TD><TD style="FONT-SIZE: 9pt">Est VPC Arr</TD><TD style="FONT-SIZE: 9pt; TEXT-ALIGN: right">2</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>L3815</TD><TD>=IF(J3815>TODAY(),1,0)+IF(J3815<TODAY()+7,1,0)</TD></TR><TR><TD>L3816</TD><TD>=IF(J3816>TODAY(),1,0)+IF(J3816<TODAY()+7,1,0)</TD></TR><TR><TD>L3817</TD><TD>=IF(J3817>TODAY(),1,0)+IF(J3817<TODAY()+7,1,0)</TD></TR><TR><TD>L3818</TD><TD>=IF(J3818>TODAY(),1,0)+IF(J3818<TODAY()+7,1,0)</TD></TR><TR><TD>L3819</TD><TD>=IF(J3819>TODAY(),1,0)+IF(J3819<TODAY()+7,1,0)</TD></TR><TR><TD>L3820</TD><TD>=IF(J3820>TODAY(),1,0)+IF(J3820<TODAY()+7,1,0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Id like this type of formula all the way down the page in column M, if there is a date in column J.

I included the information we want in case there is a better macro or eaiser way to get the information.

The data is used to make this table
Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 338px"><COL style="WIDTH: 114px"><COL style="WIDTH: 68px"><COL style="WIDTH: 68px"><COL style="WIDTH: 68px"><COL style="WIDTH: 61px"><COL style="WIDTH: 61px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>date filter</TD><TD style="TEXT-ALIGN: left">2</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Count of VM_VIN</TD><TD>Column Labels</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Row Labels</TD><TD style="TEXT-ALIGN: right">7/28/2010</TD><TD style="TEXT-ALIGN: right">7/29/2010</TD><TD style="TEXT-ALIGN: right">7/30/2010</TD><TD style="TEXT-ALIGN: right">7/31/2010</TD><TD style="TEXT-ALIGN: right">8/1/2010</TD><TD style="TEXT-ALIGN: right">8/2/2010</TD><TD>Grand Total</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: left">F</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">74</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">14</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: left">DFW AIRPORT,TX/ENTERPRISE RENT A CAR</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: left">DUNCANVILLE,TX/METROPLEX TOYOTA</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: left">GRAPEVINE,TX/TEXAS TOYOTA OF GRAPEVINE</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: left">HOUSTON,TX/NATIONAL RAC - HOBBY AIRPORT</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: left">HOUSTON,TX/OPEN ROAD RENT A CAR</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD style="TEXT-ALIGN: right">19</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

The problem is the formula needs to be in the actual macro so it isnt formatted when the macro runs.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,975
Members
430,099
Latest member
rdhoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top