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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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?
 

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.
 

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.
 

Ragnar78

Board Regular
Joined
Feb 10, 2004
Messages
210

ADVERTISEMENT

u should use in the VBA: selection.clearcontents
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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.
 

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:
 

scuzz

Board Regular
Joined
Feb 1, 2004
Messages
57
:oops: thankee kindly, i realised the same thing myself just as you posted. Thanks guys!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,101
Messages
5,768,101
Members
425,453
Latest member
bince

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