Delete cell contents without deleting the formula??

scuzz

Board Regular
Joined
Feb 1, 2004
Messages
57
Hi all,
Ive written a macro that selects all the data thats been entered e.g C2:F22, and then deletes the contents. Only trouble is, i dont know how to make a formula stay in a cell permenantly so that if the contents are deleted, the formula remains!
Anyone got a clue?
Scuzz
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Formulas *are* contents of a cell, so what you mean is to perhaps re-set all formulas to 0/""? Not sure how to do that, or even if it is possible. What is it you are trying to do?
 
Upvote 0
Well its to construct a bill for a table in a restaurant. Column E contains the price column D contains the name of the product ordered, and column C contains the quantity. In column F, i have a formula that works out the total for each product by multiplying the quantity by the price e.g. =sum(c2*e2). I have a macro which adds up all the values in colum F and then displays it as a total. I would like to be able to clear the bill of all products and prices, but retain the formula that works out the total, the =sum(c2*e2) bit.
 
Upvote 0
Do you mean you want to delete the contents of only the cells that do not contain formulas? That's Edit|GoTo|Special|Constants, click OK and press Delete. Record it and you will get the code.
 
Upvote 0
scuzz said:
Well its to construct a bill for a table in a restaurant. Column E contains the price column D contains the name of the product ordered, and column C contains the quantity. In column F, i have a formula that works out the total for each product by multiplying the quantity by the price e.g. =sum(c2*e2). I have a macro which adds up all the values in colum F and then displays it as a total. I would like to be able to clear the bill of all products and prices, but retain the formula that works out the total, the =sum(c2*e2) bit.

Probably best to have the math -- all of it -- performed via macro, then you can clear the results at any time.
 
Upvote 0
Maybe I'm missing something here but why cant you just change the range you delete using the macro to read C2:E22 instead of C2:F22

Then you dont touch the formula you seem to have in col F. col F will simply revert to zero as sum(c*e) = 0

:oops:
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top