Delete previous dates in rows with conditional formatting in VBA.

[PWil84]

Hello Everyone,

I'm trying to delete dates rows in my excel workbook using conditional formatting in VBA. I will receive the same file at the end of every month with prior months data in the file and I will only need the most recent previous month. Example in the first week of June I'll receive the file with the data for May but I won't need the data for the previous months before May. So far I recorded the Macro below and need help editing it for my desired result:

Sub DeleteInv()
'
' DeleteInv Macro
' Delete Inventory Dollar Rows
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
Rows("3:119").Select
Range("A119").Activate
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-138
Range("A2").Select
ActiveCell.FormulaR1C1 = "Date"
Range("B6").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$2:$Q$5"), , xlYes).Name = _
"Table1"
Range("A1:Q5").Select
ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type any}, {""Column2"", type any}, {""Column3"", type any}, {""Column4"", type any}, {""Column5"", type any}, {""Column6"", type any}, {""Column7"", type any}, {""Column8"", type any}, {""Column9"", type any}, {""Column10" & _
""", type any}, {""Column11"", type any}, {""Column12"", type any}, {""Column13"", type any}, {""Column14"", type any}, {""Average 12 Month Rolling Value for Converse"", Int64.Type}, {""Column15"", type any}, {""Column16"", type any}})," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""Changed Type"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type1"" = Table.Tran" & _
"sformColumnTypes(#""Promoted Headers"",{{""Date"", type datetime}, {""Hurley"", Int64.Type}, {""Con App"", Int64.Type}, {""FTW"", Int64.Type}, {""BR02 (PTO)"", Int64.Type}, {""Total"", Int64.Type}, {""Unit Cap"", Int64.Type}, {""% Capacity"", type number}, {""Column9"", type any}, {""Hurley_1"", Int64.Type}, {""Con App_2"", Int64.Type}, {""FTW_3"", Int64.Type}, {""B" & _
"R02 (PTO)_4"", Int64.Type}, {""Total Converse"", Int64.Type}, {""Column15"", Int64.Type}, {""Column16"", type any}, {""Total Con + Hur"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type1"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table1_2"
.Refresh BackgroundQuery:=False
End With
Application.CommandBars("Queries and Connections").Visible = False
Range("A3:Q4").Select
Selection.ListObject.ListRows(2).Delete
Selection.ListObject.ListRows(2).Delete
Range("I12").Select
Sheets("Total SKUS On Hand").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Inventory Count (Units)").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Inv $$$").Select
ActiveWindow.SelectedSheets.Visible = False
Range("O1").Select
ActiveCell.FormulaR1C1 = "Average 12 Month Rolling Value "
Range("B4").Select
End Sub