. Clear Contents takes out the formula for the cell i need.

[GRIM99]

I'm using the following code in the cells [CODE] =IF(ISBLANK(Z36),"",DATEDIF(Z36,TODAY(),"d")) [CODE] When I click a blank cell it clears out the formula i have in the adjacent cells. I want the formula to stay i just want the text or number to clearout.

I assume because im using the .ClearContents its clearing everything in that cell even the formula. Any ideas?
This is the vba code im using as well

[CODE]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Column
Case 10
Range("H" & Target.Row).Resize(, 2).ClearContents
Case 16
Range("N" & Target.Row).Resize(, 2).ClearContents
Case 4
Range("B" & Target.Row).Resize(, 2).ClearContents
Case 22
Range("T" & Target.Row).Resize(, 2).ClearContents
Case 28
Range("Z" & Target.Row).Resize(, 2).ClearContents
Case 34
Range("AF" & Target.Row).Resize(, 2).ClearContents

End Select

End Sub[CODE]


I hope that makes sense

[JLGWhiz]

ClearContents will remove whatever is in the designated cell, including formulas. If you want to remove the values created by the formulas, then you need to clear he contents of the precedent cell (The cell holding the value from which the formula derives the value of the target cell.) For example if you have in cell C3

Please Login or Register  to view this content.

And you want to remove the value from C3, then

Please Login or Register  to view this content.

Will change the value in C3 to empty string ("").

this all assumes that you no longer need the values in Column A and B. When using formulas to create values based on values in other cells, it is the precedent cells which determine what shows in the dependent cells. The formul in the dependent cell can be written to show blank except for specific conditions by using the IF function.

[GRIM99]

[CODE]Range("H" & Target.Row).Resize(, 2).ClearContents [CODE] what should come after that to keep the formula in the cell and just wipe the data for that cell.

[JLGWhiz]

Let me try again to explain how it works.
1. You cannot use clear contents on a cell with a formula and keep the formula.
2. You can use a formula with an IF function (eg. If(A1>0, B2, "")), to set a condition that allows the cell to appear blank if the condition is not met.
3. If you want a different value to appear in the cell, then the criteria cell (ie. A1), or the precedent cell (ie. B2) values would have to be eliminated so the formula would produce blank.
4. You have to decide how you want to make the cell appear blank and keep the formula or
5. Rethink your objective and do it a different way.

BTW, I do not respond to private messages by private message. All of my comments are provided in a post so all can see them.

[AliGW]

Administrative Note:

Not for the first time ...

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)