Date Filter Help

[pkaskaggs]

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.

 

[pkaskaggs]

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.