VBA to clear cell contents but not the formula

[soulman247]

Hello,

I am using the following code to clear the contents of a cell, as part of a form reset, but I would like to preserve a formula I have now added to this cell.

.Range("H14").Interior.Color = xlNone
.Range("H14").Value = ""

I've tried a variety of things based on a suggestion I found on the forum - below - but can't get the code compilation correct.

Selection.SpecialCells(xlCellTypeConstants, 23).ClearContents

Any guidance would be massively appreciated. Thank you

 

[StephenCrump]

You can use .SpecialCells to clear non-formula cells.

But your question implies that you want to to simultaneously clear the contents of a single cell AND maintain the formula in that single cell. That's not possible.

But depending on what you are trying to do (?) you can use VBA to clear a cell OR put a formula back into a cell, depending on circumstances.

 

[soulman247]

Hi Stephen,

Thank you for the response.

Yes, your assumption is correct, I was trying to simultaneously clear the contents of a single cell AND maintain the formula in that single cell.

I basically want to the return the cell (form) to it's 'original' state prior to the clearing of the cell/ form reset (when the cell contains a formula)

The cell reference passes data into a table and is then reset once form is 'used'.

So a cell can be cleared and then a formula put back into the cell with a new line of code? Have I Understood you correctly?

 

[Michael M]

So, can't you include in the formula to have a BLANK condition

 

[StephenCrump]

So a cell can be cleared and then a formula put back into the cell with a new line of code? Have I Understood you correctly?

Yes. I sometimes write code where a formula will appear only when circumstances are appropriate, e.g. the user has clicked a button asking for a calculation, or where all required data has been provided and validated (monitored via the Worksheet_Change event).

So, can't you include in the formula to have a BLANK condition

But Michael has a good point. Are we over-complicating what's required?

Perhaps you could provide a bit more detail?

 

[Zot]

Hello,

I am using the following code to clear the contents of a cell, as part of a form reset, but I would like to preserve a formula I have now added to this cell.

.Range("H14").Interior.Color = xlNone
.Range("H14").Value = ""

I've tried a variety of things based on a suggestion I found on the forum - below - but can't get the code compilation correct.

Selection.SpecialCells(xlCellTypeConstants, 23).ClearContents

Any guidance would be massively appreciated. Thank you

Why not just extract the formula and write it back to the cell once the cell is cleared. Maybe using a UDF?

Function GetFormula(Target As Range) As String
    If Target.HasFormula Then
        GetFormula = Target.Formula
    End If
End Function

 

[Fluff]

If you are just trying to remove the formatting, how about

Range("H14").ClearFormats

 

[soulman247]

Thanks everyone for you individual input, much appreciated.

After sleeping on it, I probably was over complicating it.

I've just read Zot's post and, although not quite as elegant, this morning I added some extras line to put the formula back once the initial sub had finished which seems to be working ok so far.

Thanks for your help folks.

 

[Zot]

Thanks for the update