1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - Clear Contents Question

Discussion in 'Business Applications' started by Frankie6925, Apr 17, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Frankie6925

    Frankie6925 Thread Starter

    Joined:
    Sep 5, 2005
    Messages:
    31
    I have Excel 2003 at home and Excel 2007 at work. I have several worksheets that I would like to copy month to month, and one really large one that I would like to copy year to year. I would like to save the formulas in each worksheet and just clear the entries that have been made. I cannot seem to find a way to do this. Thank you for your help.
     
  2. AMorrison0903

    AMorrison0903

    Joined:
    Apr 11, 2008
    Messages:
    79
    if you want to clear a range, you can create a macro and modify the range to what you need

    Sub range()
    Range("A1:Z30").Select
    Selection.ClearContents
    End Sub

    if you want to clear individual cells, you can create a macro with the following, again, just modifying the applicable cell(s)...

    Sub Cells()
    Range("A1").Select
    Selection.ClearContents
    Range("A3").Select
    Selection.ClearContents
    End Sub

    and you can create combinations of ranges with individual cells in a single macro and apply the macro to a button within the worksheet or as a short cut button on your menu...
     
  3. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    As an alternate method sans (without) code.

    Hit "f5" on the keyboard. From here click the "special" button in the bottom left-hand corner of the dialog box. Select "constants" and click "OK." Hit the "delete" key on the keyboard.
     
  4. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Hi Jim
    I thought of that, but decided it might not be a good idea, if the headings and other column descripitions needed to be retained.
    My next thought was to take the long route and delete all cell data that was not needed and then save the workbook as a template that can be opened anytime in the future for a new spreadsheet.
    Les
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I use several worksheets like that at work, and I just made a template for each, then have working copies named according to when they are for, e.g. Q1 2008 for the first quarter of 2008.
     
  6. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    With you 100% there Slurpee. Thats what I used to do when I was Management Accountant and it saved me lots of hard work.
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    The truth is, I am incredibly lazy.... ;)
     
  8. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Being lazy makes to want to find all the things that will cut your work down to nothing :D:D
     
  9. Frankie6925

    Frankie6925 Thread Starter

    Joined:
    Sep 5, 2005
    Messages:
    31
    Thank you all for your responses. Of course I was looking for the easy way out. The largest W/S took a long time to develop and took working with to make sure the formulas were working properly, and a lot of changes through the first year. I was so busy doing that I never thought about recreating it year after year. Which I have now done three times, deleting all the cells that do not contain formulas and changing all of the dates/days to correspond to the current year. You are right, my best solution is to create a template from my worksheet. I was just hoping there was a way to highlight cells and delete just contents and not formulas (without code). I have become somewhat proficient with excel, but still cannot figure out macros. I did one W/S with a very very simple macro that a wonderful person on this board did for me, but it took me three days of work arounds to make it work. Not his fault, I just didn't understand how to adjust it. So I've never really figured out how to work with them. Thank you to all of you. I've learned sooooo much from this board just by reading others questions and the answers you've supplied.
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Ways to do this:
    • Create a named range, clear the range
    • Mark the cells in an adjacent column/row
    • Use Edit | Goto (f5) | Special | Constants
    • Use VBA (last resort only)

    The VBA posted above is EXTREMELY inefficient. There is no need to select anything. I could see doing this in VBA, and I have, but a template is indeed what you need. Either that or compile all of your data into a single input location and have your outputs vary by what you are looking for.

    HTH
     
  11. AMorrison0903

    AMorrison0903

    Joined:
    Apr 11, 2008
    Messages:
    79
    I think i miss understood what needed to be done.

    Template would have been easier.
     
  12. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    Did you try what I posted earlier like Zach reaffirmed?
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/704739

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice