• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Deleting Contents, not Formulas

N

novice007

New Member
Hi Guys,


How can I delete the contents of cells, but not the formulas that I have in these cells.


So, if I had contents/formulas in cells A5 to D15, can I put these into a macro that would only delete the contents, but retain the formulas. Thanks.
 
The formula is the contents. What exactly do you mean? Perhaps you need a different formula, something like:

=IF(A2>1,A1,"")
 
Hi ,


What you are asking for is a miracle ; unfortunately even Excel cannot perform miracles !


What you can try is to have the macro make the contents of the cells invisible / hidden , so that unless the cursor is on a cell , the cell contents are not displayed ; for all practical purposes , the cell contents will be blanks , while their formulas are retained.


Narayan
 
Sorry, my poor explanation.


I suppose I mean the results are what I want to delete, and keep the formula.


I hope that makes it clearer.
 
Hi ,


Please understand that when you see the result in a cell , Excel is not storing the formula in some other place , and displaying only the result in the cell ; when you see a formula such as =A1+B1 in cell C1 , then C1 contains the formula , and depending on what the contents of A1 and B1 are , displays the result in cell C1 ; however , if you place your cursor in the cell C1 , what you see in the formula bar will be the formula in the cell.


If you remove the formula , you remove the displayed result ; also , if you remove the result , you remove the formula ; you cannot remove one of them while retaining the other.


Formatting will help to remove the displayed result while retaining the formula ; similarly , pressing F2 and then F9 , will convert the formula to its displayed result , thus removing the formula while retaining the result. However , the former is just a visual change ; the latter is a physical change , and once you have removed the formula and retained the result , you cannot bring back the formula , unless you undo your change.


Narayan
 
Hi, novice007!


Depending on the amount of data you can manually perform a delete operation on each input cell from which other cell formulas are calculated. That's to say, if C2 has a =A2+B2 formula, then delete cell contents for A2 & B2.


If your formulas may display error values for missing dependent cell values, let's say C2 be =A2/B2, then you should change it to =IF(B2=0;"";B2/C2) and then proceed as previously stated.


It's easy, it might not be quick, but I think it does what you asked for.


Regards!


PS: if you're gonna do it frequently I suggest you to create a button just to click once and have all original cells content cleared (of course you have to build the macro first, but you if you're careful the Excel built-in macro recorder will be perfect for this job)
 
SirJB7......"create a button just to click once and have all original cells content cleared" is this not the same as the clear button in the editing section of the home page!!
 
Hi Novice007,


Select the range A5:D15 and type Ctrl+G, Alt+S+O and delete.


This will delete all constants and keep the formulas.


Dheeraj
 
@Dheeraj

Hi!

I tried to mean the non-formula cells (i.e., dependency for formula cells) and as I understood from the first post the range A5:D15 has both types of content. And even if with special Ctrl-G Alt-S-O you can do the job, I think that a button is much more easy and safe to use for repeated operations with the workbook.

Regards!
 
You must log in or register to reply here.
Back
Top