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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,978
Messages
5,526,014
Members
409,675
Latest member
audeser

This Week's Hot Topics

Top