I have a table on Sheet "Daily Cases"
I have a the last date used on sheet Summary in cell B2 named "Date"
At the beginning of a new month I want to delete all the data in the Table except the Date in cel B2 named Date which I have set up as the criteria in my Code
The dates in the table are in Col from row 2 onwards
When running the macro, Col B & C are being Deleted
it would be appreciated if someone could amend my code
I have a the last date used on sheet Summary in cell B2 named "Date"
At the beginning of a new month I want to delete all the data in the Table except the Date in cel B2 named Date which I have set up as the criteria in my Code
The dates in the table are in Col from row 2 onwards
Code:
Sub Delete_TableRows()
Dim ws As Worksheet, LR As Long
With Sheets("Daily Cases")
LR = .Cells(.Rows.Count, "A").end(xlup).row
Set ws = ThisWorkbook.Worksheets("Daily Cases")
ws.Activate
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
ws.Range("A2:C" & LR).AutoFilter Field:=2, Criteria1:="<>date"
Application.DisplayAlerts = False
ws.Range("B2:C" & LR).SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
End With
End Sub
When running the macro, Col B & C are being Deleted
it would be appreciated if someone could amend my code