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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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

scuzz

Board Regular
Joined
Feb 1, 2004
Messages
57
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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Ragnar78

Board Regular
Joined
Feb 10, 2004
Messages
210
u should use in the VBA: selection.clearcontents
 
Upvote 0

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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

GS2000

New Member
Joined
Dec 26, 2002
Messages
20
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,187,178
Messages
5,962,051
Members
438,578
Latest member
MrJimC

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
Top