Automatically delete rows after time period using time stamp (have partial code)

[RainbowStorm]

Hiya

I am looking to apply a timestamp, and automatically delete the row when it is 4 years old. I can make it work to automatically delete the row within a 24 time period, but I cannot set larger periods or I am presented with a type mismatch.
This is the code I was using

Private Sub Worksheet_Activate()
Dim stampcell As Range
Dim TLSh As Worksheet
Set TLSh = Worksheets("Deselected Time Log")
Application.ScreenUpdating = False
For Each stampcell In TLSh.Range("A2:X300")
If Now > stampcell.Value2 + TimeValue("23:59:59") Then
Me.Range(stampcell.Address).ClearContents
stampcell.ClearContents
End If
Next stampcell
Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim stampcell As Range
Dim TLSh As Worksheet
Application.EnableEvents = False
Set TLSh = Worksheets("Deselected Time Log") "timestamp sheet
Set myRng = Range("A2:X300")
If Not Intersect(Target, myRng) Is Nothing Then
TLSh.Range(Target.Address).Value2 = Now
TLSh.Range(Target.Address).NumberFormat = "MM/DD/YYYY HH:MM:SS"
End If
Application.EnableEvents = True
End Sub

I identified the issue was quantifying it as time value so attempted to change it to the following:
If Now > stampcell.Value2 + DateAdd("d", 1, stampcell.Value2) Then

This was so I could test it before changing it to 6 years.

Is there anyway for me to do this?

 

[Leo Skywalker]

Hi there,

This will check for 4 years:

If Now > stampcell.Value2 + 365 * 4  Then

Posting the same question in several forums??

 

[RainbowStorm]

Hiya - I got an answer.

If Now > stampcell.Value2 + 365 * 4 Then

 

[RainbowStorm]

Hi there,

This will check for 4 years:

If Now > stampcell.Value2 + 365 * 4  Then

Posting the same question in several forums??

Hiya, sorry I wasn't aware the forum's were linked
I've reported my own posts to be removed

 

[Leo Skywalker]

Hiya, sorry I wasn't aware the forum's were linked
I've reported my own posts to be removed

I don't think they're linked. I just happen to have accounts in several Excel forums, I saw thw same question from the same user name.

 

[Leo Skywalker]

If the answer given solves your problem, please mark it as an answer.

 

[NoSparks]

Using Now for the comparison is taking the time of day into consideration so I would think the number of days would also be important.
365*4 ignores the 29th of February and will always be 1 day short of 4 years and possibly 2 days short when you change to 6 years.

I'd use the DateAdd function and Excel will look after possible leap years.

    If DateAdd("yyyy", 4, stampcell) < Now Then

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Automatically delete rows after time period using time stamp (have partial code)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.