I want records outside of the current year to be deleted.

Previously I was working on a rolling 12 month range to prove the concept. Now I need to keep only the current year's data.

I don't want to go in every month to change the month count by 1.
Is there a way of saying, if the date falls outside of 2025 remove it?

Example of date format: 13/09/2024 4:35:16 pm

Sub Delete_Rows() 'DELETE HISTORICAL DATA OVER 1 YEAR OLD
With Sheets("HistoricalData")
    lr = .Cells(Rows.Count, "C").End(xlUp).Row
    For i = lr To 2 Step -1
        If CDate(.Cells(i, "C").Value) < DateAdd("m", -12, Date) Then 'SETS THE MONTH COUNT TO MORE THAN 12 MONTHS OLD
            .Rows(i).EntireRow.Delete ' DELETES ROW
        End If
    Next i
End With
End Sub

I want records outside of the current year to be deleted.

Previously I was working on a rolling 12 month range to prove the concept. Now I need to keep only the current year's data.

I don't want to go in every month to change the month count by 1.
Is there a way of saying, if the date falls outside of 2025 remove it?

Example of date format: 13/09/2024 4:35:16 pm

Sub Delete_Rows() 'DELETE HISTORICAL DATA OVER 1 YEAR OLD
With Sheets("HistoricalData")
    lr = .Cells(Rows.Count, "C").End(xlUp).Row
    For i = lr To 2 Step -1
        If CDate(.Cells(i, "C").Value) < DateAdd("m", -12, Date) Then 'SETS THE MONTH COUNT TO MORE THAN 12 MONTHS OLD
            .Rows(i).EntireRow.Delete ' DELETES ROW
        End If
    Next i
End With
End Sub

• excel
• vba

• If Year(CDate(.Cells(i, "C").Value)) <> 2025 – CHill60 Commented Feb 5 at 13:38
• The fastest way to do it (if you don't have formulas in your data) is write the data to an array, clean the array and then write the array back to your spreadsheet. It's more code, but it's code that runs far faster. – Frank Ball Commented Feb 5 at 19:01

1 Answer 1

If you change the IF statement line to check the year of the date, your code will do the job.

 Sub Delete_Rows() 'DELETE HISTORICAL DATA OVER 1 YEAR OLD
With Sheets("HistoricalData")
lr = .Cells(Rows.Count, "C").End(xlUp).Row
For i = lr To 2 Step -1
'If CDate(.Cells(i, "C").Value) < DateAdd("m", -12, Date) Then 'SETS THE MONTH COUNT TO MORE THAN 12 MONTHS OLD
If Year(CDate(.Cells(i, "C").Value)) <> "2025" Then
.Rows(i).EntireRow.Delete ' DELETES ROW

End If
Next i
End With
End Sub