Auto deleting data after one year

G

Geo

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.
 
M

Mike H

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
 
G

Geo

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

Compile error:
Variable not defined
 
G

Geo

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?
 
C

Chip Pearson

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...
 
G

Geo

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
 
D

Dave Peterson

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...
 
G

Geo

Hi Dave,
Initialy it was: For X
on advise I added: Dim X to the code
still got error message.
 
G

Geo

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.
 
D

Dave Peterson

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...
 
G

Gord Dibben

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
 
G

Geo

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

Cheers!
 
H

HASSAN SHAREEF

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
 
Top