Macro to Delete All Data in Table(sheet Daily Cases) except Date in Cell B2 on sheet Import Data

[howard]

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

 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

 

[Michael M]

If this line

 ws.Range("A2:C" & LR).AutoFilter Field:=2, Criteria1:="<>date"

hides multiple lines, they won't be deleted !
Would changing this line

ws.Range("B2:C" & LR).SpecialCells(xlCellTypeVisible).Delete

to

ws.Range("B2:C" & LR).clear

do the trick ??

 

[howard]

Thanks Michael, but Code clears columns B & C

I wants all the rows to be deleted except the dates that are the same date as cell B2 on sheet Summary

The date currently is 30/5/2021 (dd/mm/yyyy)

See Sample data of my table below

Detail of Covid19	Attribute	Value
Coronavirus Cases	30/04/2021	1581210
Coronavirus Cases	01/05/2021	1582842
Coronavirus Cases	02/05/2021	1584064
Coronavirus Cases	03/05/2021	1584961
Coronavirus Cases	04/05/2021	1586148
Coronavirus Cases	05/05/2021	1588221
Coronavirus Cases	06/05/2021	1590370
Coronavirus Cases	07/05/2021	1592326
Coronavirus Cases	08/05/2021	1594817
Coronavirus Cases	09/05/2021	1596595
Coronavirus Cases	10/05/2021	1597724
Coronavirus Cases	11/05/2021	1599272
Coronavirus Cases	12/05/2021	1602031
Coronavirus Cases	13/05/2021	1605252
Coronavirus Cases	14/05/2021	1605252
Coronavirus Cases	15/05/2021	1611143
Coronavirus Cases	16/05/2021	1613728
Coronavirus Cases	17/05/2021	1615485
Coronavirus Cases	18/05/2021	1617840
Coronavirus Cases	19/05/2021	1621362
Coronavirus Cases	20/05/2021	1625003
Coronavirus Cases	21/05/2021	1628335
Coronavirus Cases	22/05/2021	1632571
Coronavirus Cases	23/05/2021	1635465
Coronavirus Cases	24/05/2021	1637848
Coronavirus Cases	25/05/2021	1640932
Coronavirus Cases	26/05/2021	1645555
Coronavirus Cases	27/05/2021	1649977
Coronavirus Cases	28/05/2021	1654551
Coronavirus Cases	30/05/2021	1659070
Deaths	01/05/2021	54406
Deaths	02/05/2021	54417
Deaths	03/05/2021	54452
Deaths	04/05/2021	54511
Deaths	05/05/2021	54557
Deaths	06/05/2021	54620
Deaths	07/05/2021	54687
Deaths	08/05/2021	54724
Deaths	09/05/2021	54735
Deaths	10/05/2021	54825
Deaths	11/05/2021	54896
Deaths	12/05/2021	54968
Deaths	13/05/2021	55012
Deaths	14/05/2021	55124
Deaths	15/05/2021	55183
Deaths	16/05/2021	55210
Deaths	17/05/2021	55260
Deaths	18/05/2021	55340
Deaths	19/05/2021	55507
Deaths	20/05/2021	55568
Deaths	21/05/2021	55719
Deaths	22/05/2021	55772
Deaths	23/05/2021	55802
Deaths	24/05/2021	55874
Deaths	25/05/2021	55976
Deaths	26/05/2021	56077
Deaths	27/05/2021	56170
Deaths	28/05/2021	56293
Deaths	30/04/2021	54350
Recovered	01/05/2021	1506732
Recovered	02/05/2021	1507778
Recovered	03/05/2021	1508558
Recovered	04/05/2021	1509656
Recovered	05/05/2021	1510385
Recovered	06/05/2021	1511905
Recovered	07/05/2021	1513202
Recovered	08/05/2021	1514088
Recovered	09/05/2021	1516256
Recovered	10/05/2021	1517350
Recovered	11/05/2021	1519258
Recovered	12/05/2021	1519734
Recovered	13/05/2021	1519734
Recovered	14/05/2021	1519734
Recovered	15/05/2021	1519734
Recovered	16/05/2021	1524352
Recovered	17/05/2021	1526638
Recovered	18/05/2021	1527968
Recovered	19/05/2021	1528868
Recovered	20/05/2021	1531993
Recovered	21/05/2021	1534350
Recovered	22/05/2021	1537430
Recovered	23/05/2021	1539395
Recovered	24/05/2021	1541250
Recovered	25/05/2021	1543951
Recovered	26/05/2021	1546583
Recovered	27/05/2021	1548092
Recovered	28/05/2021	1551520
Deaths	30/05/2021	56363
Recovered	30/04/2021	1505620
Recovered	30/05/2021	1554184

 

[howard]

Hi Michael

Not sure if you saw my my table in post # 3 ? It would be appreciated if you will make the necessary changes

 

[Michael M]

Is this table also on Sheet "Summary"?
If not what is the sheet name

 

[Michael M]

Try this

Sub MM1()
Dim lr As Long, r As Long
lr = Sheets("Daily Cases").Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Daily Cases")
For r = lr To 2 Step -1
If .Cells(r, 2) <> Sheets("Summary").Cells(2, 2) Then
.Rows(r).Delete
End If
Next r
End With
End Sub

 

[howard]

Thanks for the help Code works perfectly

 

[Michael M]

Glad I could help....