# can I clear a cell's contents and retain the formula in that cell?

#### keithmct

##### Board Regular
I have a range of cells on one input sheet that is automatically copied over to an order form. Formula in order form cells is =Home!E14 etc.
Now that we have 2 suppliers I need to remove (clear contents of that row or range) orders on the order form, column J, that starts with JPM, but clearing contents deletes the formula =Home!E14
any ideas?
I've looked at Go To Special which doesn't apply as far as I can tell.
I've used this code:

https://www.rondebruin.nl/win/s4/win001.htm

from Ron De Bruin which deletes the rows but then I'd have to add back rows with formulas in them to my order template for other things to work properly.
I'm pretty stumped at this stage.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Jeffrey Mahoney

##### Well-known Member
If you clear the contents at Home!E14, then the formula will result in NULL.

So no, you can't clear the cell with a formula and have it retain the formula.

#### keithmct

##### Board Regular
I dont want to clear Home!E14, I want to clear the cell on the order form that looks at Home!E14 and duplicates the value there. Then I run some sort of code to remove the unwanted stuff from the order form sheet & the next time I use the spreadsheet it all has to go back to normal again.

#### MARK858

##### MrExcel MVP
You can't clear a cell and retain a formula. By the sound of it you either need to use code to replace the formula or look at setting up the order form as a template.

Replies
6
Views
324
Replies
7
Views
398
Replies
1
Views
321
Replies
17
Views
871
Replies
3
Views
140

1,196,017
Messages
6,012,870
Members
441,737
Latest member
bijayche

### 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.

### Which adblocker are you using?

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

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