VBA to delete rows that are older than 14 days

I have the following VBA to delete rows with data entered 14 days ago. The problem with it is that it takes up to 5 seconds for the script to run. Question: is there a way to speed it up? I ha...

Sample.xlsx359 KB

Macros and VBA

HansVogelaar

Dec 24, 2022

Guram_Bregadze

Try this version:

Sub DeleteRows()
    Dim i As Long, LastRow As Long, t As Double, r As Range
    Application.ScreenUpdating = False
    t = Timer
    With ActiveSheet ' Sheets("Sheet1")
        LastRow = .Range("P" & .Rows.Count).End(xlUp).Row
        For i = LastRow To 2 Step -1
            If .Range("P" & i).Value < Date - 14 Then
                If r Is Nothing Then
                    Set r = .Range("A" & i)
                Else
                    Set r = Union(r, .Range("A" & i))
                End If
            End If
        Next i
    End With
    r.EntireRow.Delete
    t = Timer - t
    Application.ScreenUpdating = True
    Debug.Print t
End Sub

Forum Discussion

VBA to delete rows that are older than 14 days