VBA - Delete All Rows Where Any Cell In Column E does not Equal Yesterdays Date

[PatrickW1907]

Hi Guys,

I'm hoping someone can help.

I have over 10k rows of data.

A list of dates in column E (uk format i.e. dd/mm/yyyy)

I want to delete any row (except for header) that does not have yesterdays date. I have tried doing Date-1 and then I have tried to get a cell value after i put a formula to get yesterdays date then copied and pasted values into AI1. As I have over 10K data I am trying to do the quickest way possible as For Loops and IFs take an age to run.

The closest I get is the following code

Sub Test6()

Worksheets("Sheet1").Activate

Range("AI1").Formula = "=TODAY()-1"

Range("AI1").Copy
Range("AI1").PasteSpecial xlPasteValues

With ActiveSheet
    .Range("A:AH").AutoFilter
    .AutoFilter Field:=5, Criterial:=Range("<>AI1").Value
   
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
End With


 
End Sub

At the moment I get the error runtime error 1004 : Method range of object - 'Global' failed but I also have had named object error.

Any help is appreciated.

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

Does this work for you?

Sub Test7()
  With Worksheets("Sheet1").Range("A:AH")
      .AutoFilter Field:=5, Criteria1:="<>" & CLng(Date - 1)
      .Resize(Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
      .AutoFilter
  End With
End Sub

 

[PatrickW1907]

With Worksheets("Sheet1").Range("A:AH") .AutoFilter Field:=5, Criteria1:="<>" & CLng(Date - 1) .Resize(Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete .AutoFilter End With

Hi Peter

Thanks for this. This is exactly what I am looking for

Apologies for the tags ! I didn't know they were there lol. Know for next time. Thanks again !

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.