| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto deleting data after one year
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
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 -- Geo "Chip Pearson" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
What line causes the error?
Geo wrote: 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 -- Geo "Chip Pearson" wrote: 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 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
Hi Dave,
Initialy it was: For X on advise I added: Dim X to the code still got error message. -- Geo "Dave Peterson" wrote: What line causes the error? Geo wrote: 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 -- Geo "Chip Pearson" wrote: 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
And what line gets the error now?
Geo wrote: Hi Dave, Initialy it was: For X on advise I added: Dim X to the code still got error message. -- Geo "Dave Peterson" wrote: What line causes the error? Geo wrote: 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 -- Geo "Chip Pearson" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
ps. The code worked ok for me.
Geo wrote: Hi Dave, Initialy it was: For X on advise I added: Dim X to the code still got error message. -- Geo "Dave Peterson" wrote: What line causes the error? Geo wrote: 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 -- Geo "Chip Pearson" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
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" wrote: ps. The code worked ok for me. Geo wrote: Hi Dave, Initialy it was: For X on advise I added: Dim X to the code still got error message. -- Geo "Dave Peterson" wrote: What line causes the error? Geo wrote: 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 -- Geo "Chip Pearson" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
Try deleting that line and retyping it.
If that doesn't work, post the current version of your code. Geo wrote: 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" wrote: ps. The code worked ok for me. Geo wrote: Hi Dave, Initialy it was: For X on advise I added: Dim X to the code still got error message. -- Geo "Dave Peterson" wrote: What line causes the error? Geo wrote: 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 -- Geo "Chip Pearson" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
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 On Sat, 13 Oct 2007 09:51:32 -0500, Dave Peterson wrote: ps. The code worked ok for me. Geo wrote: Hi Dave, Initialy it was: For X on advise I added: Dim X to the code still got error message. -- Geo "Dave Peterson" wrote: What line causes the error? Geo wrote: 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 -- Geo "Chip Pearson" wrote: 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 -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
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 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto deleting data after one year
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
| Months auto-filter with year twist | New Users to Excel | |||
| Excel 2002 auto deleting | Excel Discussion (Misc queries) | |||
| auto deleting defined rows | Excel Discussion (Misc queries) | |||
| Auto Year scale | Charts and Charting in Excel | |||
Linear Mode
