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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,107,005
Messages
5,514,759
Members
409,019
Latest member
SaKoya

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top