Auto deleting data after one year

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What is the code for deleting data that is 1 year old ( a rolling year).
Dates are on column A and data runs from columns B to P.
 
Maybe,

Right click the sheet tab, view code paste this in and run it:-


Sub deleteit()
lastrow = Range("A65536").End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, 1).Value < Date - 365 Then
Range("A" & x & ":P" & x).ClearContents
End If
Next
End Sub

Mike
 
Thanks Mike,
Did that, but everytime I run it it comes back with the following error:

Compile error:
Variable not defined
 
Thanks Dave but I still get the same error message but this time it
highlights 'x' in the code?
Has that got to change as well?
 
The code supplied does not declare the variable 'x' and most likely you have
an Option Explicit statement at the top of you module, which requires that
all variables be declared. Add the following to the procedure:

Dim X
' rest of code

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


Geo said:
Thanks Dave but I still get the same error message but this time it
highlights 'x' in the code?
Has that got to change as well?
Click to expand...
 
Sorry Chip I get the following error message:

RunTime error 1004
Application-defined or object-defined error

Iv got the code as follows:

Sub deleteit()
Dim X
Dim LastRow As Long
LastRow = Range("A60000").End(xlUp).Row

For X = LastRow To 1 Step -1
If Cells(X, 1).Value < Date - 365 Then
Range("A" & X & ":H" & X).ClearContents
End If
Next
End Sub
 
What line causes the error?
Sorry Chip I get the following error message:

RunTime error 1004
Application-defined or object-defined error

Iv got the code as follows:

Sub deleteit()
Dim X
Dim LastRow As Long
LastRow = Range("A60000").End(xlUp).Row

For X = LastRow To 1 Step -1
If Cells(X, 1).Value < Date - 365 Then
Range("A" & X & ":H" & X).ClearContents
End If
Next
End Sub
Click to expand...
 
Hi Dave,
Initialy it was: For X
on advise I added: Dim X to the code
still got error message.
 
Its Dim X gives me the error

But if I romove "Dim X"
the error high lights X of the For X part of the code.
 
Try deleting that line and retyping it.

If that doesn't work, post the current version of your code.
Its Dim X gives me the error

But if I romove "Dim X"
the error high lights X of the For X part of the code.
--
Geo

Dave Peterson said:
ps. The code worked ok for me.
Click to expand...
Click to expand...
 
Also works for me.

Just a note to Geo............Mike's instructions to paste the macro into the
Sheet module should be changed to paste the macro into a general module.

Leave sheet modules for event code.


Gord Dibben MS Excel MVP
 
Thanks Dave and Gord,
I did retype the code and it works fine now.
Thanks very much for your help and patients.

Cheers!
 
Geo,
this is Hassan.
Without going into details and on a smaller sample data I tried Mike's first
code it worked without any err.
regards
 
Back
Top