CODEWITHSUNDEEP
vbaexcel
superblowncolon
superblowncolon

Reputation: 185

Excel VBA: Delete Old Records

I'm attempting to delete any records that are 180 days or older. The dates are in Column F. When I run this, nothing happens. I'm thinking that it has to do with the Date() function.

Sub ClearOldData()
Application.ScreenUpdating = False
Sheets("Data").Select
Dim LastRow As Long
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = 2 To LastRow
    Dim recdate As Date
    recdate = Cells(i, "F").Value 
    If DateDiff(d, Date, recdate) > 179 Then
        ws.Rows(i).Delete
    End If
Next i
Application.ScreenUpdating = True
End Sub

Upvotes: 1

Views: 1368

Answers (1)

user6432984
user6432984

Reputation:

In the VBA Date/Time is a numeric value starting at 1 which equates to #1/1/1900#.

Date/Time Values

  • 1 Day = 1
  • 1 Hour = 1/24
  • 1 Minute = 1/24/60
  • 1 Second = 1/24/60/60

As always delete from the last row/item to the first. My answer to Compare cells to delete rows, value is true but not deleting rows demonstrates why.

DateDiff("d", Date, recdate) > 179 and Int(Date - recdate) > 179 are equivalent. 

Sub ClearOldData()
    Application.ScreenUpdating = False
    Dimi As Long

    With Sheets("Data")

        For i = 2 To LastRow Step -1
            If Int(Date - Cells(i, "F").Value) > 179 Then
                .Rows(i).Delete
            End If
        Next i

    End With
    Application.ScreenUpdating = True
End Sub

Upvotes: 3

Related Questions