Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Question Clearing values but not formulas ?

    I want to make an existing spreadsheet into a template for a worksheets in a spreadsheet, so I opened the existing spreadsheet, renamed it, picked one of the worksheets to use as the template and deleted all the other individual worksheets. I then wanted to clear all the data out of the spreadsheets, but leave the row and column headings and all the formulas in place so that they would be there when I used this "template" next time. I found that "Clear Contents" does just that - it removes everything except the formatting. So my question is - how do I clear the numbers out of the spreadsheet, but leave the formulas in place. I know how to leave the row and column headings in place - just don't select them for clearing. Can this be done without having to write a macro - my VBA skills are very minimal - I can read the code and that is about it. Any insight that anyone would care to provide will be greatly appreciated. Thanks.

    Ron M

  2. #2
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Try locking the cells with formula in and then clearing it out.

    Alan

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Select the range of interest
    Home - Find & Select
    Goto Special
    Select "Constants"
    Uncheck all but "numbers"
    [ok]
    Now only the constant numbers are selected, no const text, no formulas
    <Delete>
    Now the contents are cleared

    Steve

  4. The Following User Says Thank You to sdckapr For This Useful Post:

    RetiredGeek (2014-01-14)

  5. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    Quote Originally Posted by alan sh View Post
    Try locking the cells with formula in and then clearing it out.

    Alan
    This does not seem to work, unless I am doing something wrong.

    Ron

  6. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    Quote Originally Posted by sdckapr View Post
    Select the range of interest
    Home - Find & Select
    Goto Special
    Select "Constants"
    Uncheck all but "numbers"
    [ok]
    Now only the constant numbers are selected, no const text, no formulas
    <Delete>
    Now the contents are cleared

    Steve
    So far so good. Thanks Steve, I appreciate your help with this.

    Ron M :0

Posting Permissions

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