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

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
"Geo" wrote:

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

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

Compile error:
Variable not defined


--
Geo


"Mike H" wrote:

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
"Geo" wrote:

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

Sub deleteit()
dim LastRow as long
...rest of code here
End Sub

Geo wrote:

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

Compile error:
Variable not defined

--
Geo

"Mike H" wrote:

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
"Geo" wrote:

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

--

Dave Peterson

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


"Dave Peterson" wrote:

Sub deleteit()
dim LastRow as long
...rest of code here
End Sub

Geo wrote:

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

Compile error:
Variable not defined

--
Geo

"Mike H" wrote:

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
"Geo" wrote:

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

--

Dave Peterson

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 procedu

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" wrote in message
...
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?
--
Geo


"Dave Peterson" wrote:

Sub deleteit()
dim LastRow as long
...rest of code here
End Sub

Geo wrote:

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

Compile error:
Variable not defined

--
Geo

"Mike H" wrote:

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
"Geo" wrote:

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

--

Dave Peterson

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

Cheers!
--
Geo


"Mike H" wrote:

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
"Geo" wrote:

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

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

"Geo" wrote:

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

Cheers!
--
Geo


"Mike H" wrote:

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
"Geo" wrote:

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