auto erase data

Say I have a list like this

5/16/2011 absent 3 points
5/27/2011 absent 3 points

is there a macro I can write in that will auto delete the last entry after a set period of time? What happens is if a person goes 90 days without an occurrence the last one gets knocked off. Any code thats able to do that?

Thanks

still needing help with this. Is it possible for something like..

if last cell with an entry and is > than 89 days then delete?

messiah166,

Is something like this what you're looking for? (If the dates are in a different column, just change the DateCol to the correct column.)

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

thanks tiger for the response. but would this line:
If Now - rngLast.Value > 89 Then rngLast.EntireRow.Delete xlShiftUp
delete the entire row all the way across the sheet?

that would be highly undesirable as it would ruin the entire sheet.

sheet is now attached. thank you.

the lines that would need to be erased are cells W,X,Y, and Z all the way down.

going from what you wrote would this work?

Please Login or Register  to view this content.

messiah166,

Looking at the file you attached, you could modify it to the following, which should work for you:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

Hi tiger did you try the code in the sheet? I inserted the code and changed the 89 to 1 to test it then put in yesterdays date. Closed reopened the sheet but its not removing it. Any idea what could be wrong?

messiah166,

I did test it. The issue you're experiencing is because the condition is not >= (greater than or equal to), its just > (greater than). So if something is only 1 day old, that is not >1 and so it will not be removed. Try something 2 days older to test it out.

Hope that helps,
~tigeravatar

I see the problem. If I manually run the macro it erases. But its not auto erasing when the sheet is closed and reopened. Incorporate it into a worksheet_change?

messiah166,

Yeah, a worksheet change could do it, though it would run far more often than necessary. From your description of closing and opening the workbook, I'd put it in the Workbook_Open event.

~tigeravatar

Please Login or Register  to view this content.

messiah166,

Looks good to me

~tigeravatar

I got it thanks much