Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    16

    Unanswered: clearing cells and saving the formula

    I am trying to clear the contents of cells without losing the formula in the cell. i have tried to lock the worksheet, but then i can't edit or add new data. is there something different i can do to keep the formulas when i clear the cells?

    thanks
    knottrodney

  2. #2
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by knottrodney
    I am trying to clear the contents of cells without losing the formula in the cell. i have tried to lock the worksheet, but then i can't edit or add new data. is there something different i can do to keep the formulas when i clear the cells?

    thanks
    knottrodney
    Try this, but please MAKE a copy of your workbook first!!!
    Example all your formula are in Sheet1

    Worksheets("Sheet1").Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents

  3. #3
    Join Date
    May 2006
    Posts
    16
    where do i place this code? is there a sub that I run the code under or is there another way to do it in excel, possibly a macro?

    thanks,
    knottrodney

  4. #4
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by knottrodney
    where do i place this code? is there a sub that I run the code under or is there another way to do it in excel, possibly a macro?

    thanks,
    knottrodney
    oh well..depends how you want it to be, if u want to have a click of the button, then make a button, lets call it cmdDelete, then u should put the code in cmdDelete_Click().

    if u want only when u open the workbook that time, it clear away the value, then put it in Workbook_Open()

    Yes, there is somewhere in the excel u can write code, it is under Tools->Marco try out the marco (we called it vba) then u will find out that is much more excel can do for u.

    Have fun!!

  5. #5
    Join Date
    Jun 2006
    Posts
    103

    example on delete without clearing formula

    here i attach one example for you
    Attached Files Attached Files

  6. #6
    Join Date
    Jun 2006
    Posts
    103
    if u prefer to do it manually, try this

    select all the content of your sheet, include value and formula, everything

    then press Alt-E, then press G, then Alt-S. Lastly, choose contants. then just delete

    got it?

Posting Permissions

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