+ Reply to Thread
Results 1 to 9 of 9

Delete values without deleting formulas

  1. #1
    Registered User
    Join Date
    09-18-2017
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    4

    Delete values without deleting formulas

    Hi,

    I want to make a small ‘program’ in Excel that I can use to include patients in a study.

    The program contains 5 columns (see attachment):
    Visit # -- Date -- # days -- # years -- Value
    The 3rd row, # days, is calculated from the second row, Date. The fourth row, #years, is calculated from the 3rd row, # days.

    I want to use this for multiple patients. So, I would like to be able to delete at once all the values in the columns date, # days, # years, and value, without deleting the formulas in the third and fourth row.

    Can anyone help me with this?

    Thanks in advance!
    M.
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Delete values without deleting formulas

    you cant delete formulas and expect them, to reappear
    perhaps in c3 if(b3="","",your current formula) etc
    Last edited by davsth; 09-18-2017 at 12:24 PM.

  3. #3
    Registered User
    Join Date
    09-18-2017
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    4

    Re: Delete values without deleting formulas

    No, I am saying I want to delete the calculated values but not the underlying formulas.

    So I want the values in colums B, C, D, and E to be deleted without deleting the underlying formulas in C and D.

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Delete values without deleting formulas

    did you try the formula, if the values in column b are blank, the corresponding row would be blank c3 might be if(b3="","",b3-b2) etc

  5. #5
    Registered User
    Join Date
    09-18-2017
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    4

    Re: Delete values without deleting formulas

    Sorry, I appreciate the help but I don't understand what you mean.

    The formula in C3 is now: =B3-B2
    In C4: =B4-B2
    And so on.

    In D3: =C3/365
    In D4: =C4/365
    And so on.

    What exactly do you mean with 'the corresponding row would be blank c3 might be if(b3=",",b3-b2) etc'?

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Delete values without deleting formulas

    Seems to me that when you delete the dates in column B the formula results are all set to 0 thus.

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  7. #7
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Delete values without deleting formulas

    as bakerman says clear the range b2:B( and the end)

    then your formula in c3 =B3-B2 you change to what I suggested =if(b3="","",b3-b2) this will then display blank when there are no values present. In all cases the formula in c to e is c3 if(b3="","",your current formula) etc

  8. #8
    Registered User
    Join Date
    09-18-2017
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    4

    Re: Delete values without deleting formulas

    @ Bakerman2: thanks for that brilliant piece of advice. I feel a bit ashamed that I didn't think of it myself! :$

    @ davsth: when I try your formula, it says "The formula you typed contains an error". I don't know what I'm doing wrong but it doesn't work. Anyway: thanks for your advices, to the both of you. I'll use bakerman2's way and will just have to delete the values in column B and E and that 'll be it.

    Thanks!

  9. #9
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Delete values without deleting formulas

    You're welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Help with Macro to delete data without deleting formulas on same sheet
    By Pi* in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2014, 11:58 PM
  2. Replies: 4
    Last Post: 08-12-2013, 01:35 PM
  3. Updated column values without deleting formulas
    By bpercival in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2012, 12:29 AM
  4. delete only cell values (not formulas) in multiple sheets
    By alexandruc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-03-2009, 05:05 AM
  5. VBA to delete Formulas, Names, & Macros of another workbook, but to keep values
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-16-2009, 03:03 PM
  6. deleting values in a worksheet without deleting the formulas
    By patti in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-28-2005, 05:05 PM
  7. [SOLVED] how do I delete numbers without deleting formulas
    By Emmaus junkie in forum Excel General
    Replies: 1
    Last Post: 10-08-2005, 05:05 PM

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