clearing cells w/out clearing the formulas, formats

Tom F

Active Member
Joined
Oct 19, 2002
Messages
263
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
Or with a Marco
Sub Clear_Constants()
Selection.SpecialCells(xlCellTypeConstants, 23).ClearContents
End Sub
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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>

My email address is tfinnie@hotmail.com

Many thanks,
Tom F
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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