How to clear cell content older than a year (or more)

**myexcelstar** · 01-08-2013, 04:42 PM

Hi there,
I have a spread sheet with certification listings of staff. Those expire after a certain amount of years. How can I set up my sheet that it deletes (clears the cells) any expired certification dates?
Example: Certificate A expires every 2 years
Certificate B expires every 3 years
Certificate C expires every 5 years
What I would like to happen is, that upon opening of the file a dialog box comes up and asks: "Would you like to delete old Certificate data?" along with a "YES" and "CANCEL" button. If I click "YES", it deletes all dates in column D, E & F that are older than 2, 3 and 5 years. The formatting should NEVER change in those columns though (so I guess it is ClearContents?!). The dialogue box would come up every time the worksheet is being opened.
Can this be done???
Thanks so much for your time & help!

**watersev** · 01-08-2013, 05:42 PM

hi myexcelstar, please check attachment

**Tsjallie** · 01-08-2013, 05:42 PM

Guess this will do the trick.

Please Login or Register  to view this content.

**cytop** · 01-08-2013, 06:16 PM

Cross posted (and infraction given): http://www.ozgrid.com/forum/showthread.php?t=173569

**myexcelstar** · 01-10-2013, 10:09 PM

Hi watersev... thanks so much, I have been trying to make it work, but for whatever reason, it keeps deleting all data older than 2 years in all rows?! I think I might have miscommunicated that. I want it to delete in column D all that are older than 2 years, in column E all that are older than 3 and in column F all that are older than 7 years. Is it possible to modify it in that way?

Can I also determine like a final row for the condition? I don't want the script to look below that row as there might be dates I don't want them to be deleted.

Thanks so much for your help.

**myexcelstar** · 01-10-2013, 10:13 PM

Hi Tsjallie, I am getting an error message: "Method 'Range' of object '_Global' failed. How would the script know what "CertA" is? Can I tell the script only to look in a certain range of rows (eg. in column D only in rows 4-932)??
Thanks

**Winon** · 01-13-2013, 06:20 AM

Hello myexcelstar,

Please take a moment to read the Forum Rules again, especially regarding "Cross Posting".

Cross posted (and infraction given): http://www.ozgrid.com/forum/showthread.php?t=173569

cytop caught you out, and you will appreciate that Forums don't take kindly to this behavior, as people from different Forums, who are offering their time and knowledge, may find duplicating each other - which is a waste of time and effort.

Unfortunately, I now feel obliged, not to try and help you with this Thread, as a few other Forum members have already offered you some solutions, and I will leave it up to them to decide if they wish to continue assisting you.

Do the right thing. Apologise, and show in which other Forums you have posted this thread as well.

**myexcelstar** · 01-13-2013, 10:40 AM

Hi Winon, thanks. Fully understand, no problem.

Never intended to waste anyone's time, and if I did, sorry for that. Was just trying to get a fast response to a project I needed to complete. Guess I will retire it now. Anyway, thx again for the help I received.

**Winon** · 01-13-2013, 03:34 PM

Hi myexcelstar,

...show in which other Forums you have posted this thread as well.

and you would be most likely to receive the ultimate solution you are after.

Happy Coding.

How to clear cell content older than a year (or more)

LinkBack

Thread Tools

Rate This Thread

Display

How to clear cell content older than a year (or more)

Re: How to clear cell content older than a year (or more)

Re: How to clear cell content older than a year (or more)

Re: How to clear cell content older than a year (or more)

Re: How to clear cell content older than a year (or more)

Re: How to clear cell content older than a year (or more)

Re: How to clear cell content older than a year (or more)

Re: How to clear cell content older than a year (or more)

Re: How to clear cell content older than a year (or more)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions