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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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
Joined
Feb 15, 2002
Messages
575
Or with a Marco
Sub Clear_Constants()
Selection.SpecialCells(xlCellTypeConstants, 23).ClearContents
End Sub
 

Tom F

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

ADVERTISEMENT

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

ADVERTISEMENT

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
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Feb 15, 2002
Messages
575
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
Joined
Oct 19, 2002
Messages
263
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
 

Forum statistics

Threads
1,144,127
Messages
5,722,634
Members
422,450
Latest member
Springbok

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