Clearing a single Cell without deleting the contained formula [Closed]

Report
Posts
1
Registration date
Wednesday January 15, 2014
Status
Member
Last seen
January 15, 2014
-
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
-
Hello, this is my very first try at using a formula for help (so please be patient with me if I make a mistake:) I have a growing knowledge of Excel and a very adolescent grasp of VBA. Having said that, I have created a sort of a calculator on Excel to assist me and those in my office with estimating for our excavating company. I have about ten fields left "unlocked" on the sheet for users to enter values (both drop lists of words as well as numbers) that then provides enough info for the calculator to figure out the appropriate layouts and material measurements etc. I have created a "Clear Sheet" macro button that can be clicked to clear all those fields and start over. I have made a basic module that selects the cell by ranges and then "ClearContent"s. However using that approach dumps the formulas as well which has forced me to write long function commands to make sure none of the results are dependent on formulas in those accessible cells. Is there a command that would clear those cells without erasing the formulas contained? Or not? Thanks.

1 reply

Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi Mwprimerica,

Let's look at an example:
A1: 1
A2: =A1

A2 will show 1 as result.
When the 1 is removed from A1, A2 now shows 0 as result.

Form what I understand from your query, is that you do not want to see that 0.

1) You could use conditional format.
Formula is: =A2=0
Format: text color set to white

2) You could use IF formula.
=IF(A1=0,"",A1)

PS: next time divide your query in paragraphs for easier reading.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!