Delete values and keep formula vba code

[RHB]

I have cells M31:O54 where I want to delete values but keep formulae.
Can someone show me where my code is incorrect.
I'm pretty basic in understanding code.

Range("O5:AH28,P56:AG79").Select
Selection.ClearContents
Range("M31:O54").Select
Selection.SpecialCells(xlCellTypeConstants).ClearContents
End Sub

Thanks for any help.

 

[StephenCrump]

This line will clear the contents of non-formula cells in ActiveSheet.Range("M31:O54"):

Range("M31:O54").SpecialCells(xlCellTypeConstants).ClearContents

You can do similar for the discontiguous Range("O5:AH28,P56:AG79") if you wish.

To allow for the possibility of no cells of type xlCellTypeConstants in the range(s), you should wrap like this, to avoid your code erroring:

On Error Resume Next
Range("M31:O54").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0

 

[RHB]

This line will clear the contents of non-formula cells in ActiveSheet.Range("M31:O54"):

Range("M31:O54").SpecialCells(xlCellTypeConstants).ClearContents

You can do similar for the discontiguous Range("O5:AH28,P56:AG79") if you wish.

To allow for the possibility of no cells of type xlCellTypeConstants in the range(s), you should wrap like this, to avoid your code erroring:

On Error Resume Next
Range("M31:O54").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0

This line will clear the contents of non-formula cells in ActiveSheet.Range("M31:O54"):



VBA Code:

Range("M31:O54").SpecialCells(xlCellTypeConstants).ClearContents

Thank you, but I want to clear the cells containing formulae of their values but keep the formulae.

 

[StephenCrump]

Thank you, but I want to clear the cells containing formulae of their values but keep the formulae.

A cell can't be both blank and contain a formula.

You could use VBA to toggle between blank and formula depending on conditions.

Or the cell could contain a formula that showed blank under certain conditions, e.g. the cell formula could be:

=IF(ShowBlanks,"",SomeFormula)

and you could use VBA to toggle between Names("ShowBlanks").RefersTo = True and Names("ShowBlanks").RefersTo = False as required.

 

[RHB]

Thanks Stephen, I didn't know as I thought I'd read where you could "clear" the contents but keep the formulae.
Not to worry, I think I have another work around by not using VBA and reference to other cells to do the job.
But thank you for getting back to me.