+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    03-29-2012
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    42

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

    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!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

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

    hi myexcelstar, please check attachment
    Attached Files Attached Files

  3. #3
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

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

    Guess this will do the trick.
    Please Login or Register  to view this content.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  4. #4
    Forum Expert (Lazarus)
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    .
    Posts
    2,594

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

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

  5. #5
    Registered User
    Join Date
    03-29-2012
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    42

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

    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.
    Last edited by myexcelstar; 01-10-2013 at 11:37 PM.

  6. #6
    Registered User
    Join Date
    03-29-2012
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    42

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

    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
    Last edited by myexcelstar; 01-10-2013 at 11:42 PM.

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

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

    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.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  8. #8
    Registered User
    Join Date
    03-29-2012
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    42

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

    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.

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1