# clearing cells w/out clearing the formulas, formats

#### Tom F

##### Active Member
I have a schedule to demonstrate. The start time and end time are entered by the user after which the remaining information is calculated and displayed on the worksheet.

A blank worksheet is desired to begin without deleting the formulas or formats. Is it possible to clear the cells without clearing the formulas within the cells? If so, how?

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### plettieri

##### Well-known Member
Hello Tom F

Welcome to the Board

You might try this....

Select the cells
Go to Edit>GoTo>Special>Constants and click OK
Press the Delete key

hope this helps

pll

#### Paul B

##### Well-known Member
Or with a Marco
Sub Clear_Constants()
Selection.SpecialCells(xlCellTypeConstants, 23).ClearContents
End Sub

#### Tom F

##### Active Member
Plettieri, I tried your solution with no success. Perhaps I am not doing something right.

Paul B, perhaps now is the time to learn something about macros. Do you know of any other ways?

#### Paul B

##### Well-known Member
Tom F, plettieri solution should work, you do not have to select the cells first, just click on edit, goto, or press ctrl and G, special constants and click then press the delete key. The Marco just does all of this for you. Paul

#### matthume

##### New Member
I am not sure how extensive the range is that you want to blank. It can be done by modifying the underlying formulas to evaluate to "" or 0 if the start and finish time are missing.

Assume that start time is in A1, finish is in A2, A3 contains =OR(ISBLANK(A1),ISBLANK(A2)) and A5 contains =IF(A3,"",CONCATENATE("Display Results ",+A2-A1)).

If either cell is blank, Display results will not appear.

You would have to modify each of the formulas by adding =IF(\$A\$3,"", to the beginning of each formula, and add a parenthesis at the end. Change the \$A\$3 reference to the cell that contains your logical test.

It is kind of a brute force approach, but I use it quite a bit to spare users a screen full of #VALUE messages.

#### Tom F

##### Active Member
Paul,

I tried plettieri's solution with no success.

The schedule allows one to enter a start time and an end time into the respective cells after which the corresponding cells are calculated and displayed.

It is my wish to start with the formulas in the cells and no values displayed.

Many thanks,
Tom F.

#### Yogi Anand

##### MrExcel MVP
Hi Tom F:

As plettieri stated and Paul restated the strategy suggested by plettieri should work -- just do EDIT|GOTO|SPECIAL|CONSTANTS ... select Numbers -- click OK, then EDIT|CLEAR|CONTENTS

so that you only delete the NUMBER constants entered and leave the Text and Formula entries alone.

Another option would be to delete all the user input entries by

selecting an empty cell -- do EDIT|COPY
then select the user input entries you want to delete -- then EDIT|PASTE_SPECIAL|MULTIPLY

this would zero out all user input entries.

But the first method as suggested by plettieri is better.

Regards!

Yogi

#### Paul B

##### Well-known Member
If your start time is in A1 and end time in B1, put this in C1, =IF(B1,B1-A1,"") this will show blank until you put data in B1. Is this what you are looking for?

#### Tom F

##### Active Member
Umm ... would anyone be interested in receiving this excel spreadsheet as an email and attempting it for yourself?

If you are please send me an email and I will speedily send it to you. It is quite a simple and straightforward worksheet but eludes me as how to blank the cells for the input and calculated values. <scratching head>

Many thanks,
Tom F

Replies
1
Views
1K
Replies
27
Views
2K
Replies
0
Views
350
Replies
3
Views
1K
Replies
1
Views
2K

1,181,246
Messages
5,928,876
Members
436,634
Latest member
JimHHH

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