Clearing a single Cell without deleting the contained formula

Closed
mwprimerica Posts 1 Registration date Wednesday January 15, 2014 Status Member Last seen January 15, 2014 - Jan 15, 2014 at 04:54 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Jan 16, 2014 at 11:31 AM
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

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Jan 16, 2014 at 11:31 AM
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
0