Delete values and keep formula vba code

RHB

Board Regular
Joined
Feb 11, 2010
Messages
66
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
This line will clear the contents of non-formula cells in ActiveSheet.Range("M31:O54"):

VBA Code:
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:

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

RHB

Board Regular
Joined
Feb 11, 2010
Messages
66
This line will clear the contents of non-formula cells in ActiveSheet.Range("M31:O54"):

VBA Code:
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:

Code:
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

RHB

Board Regular
Joined
Feb 11, 2010
Messages
66
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,976
Messages
5,656,184
Members
418,288
Latest member
reba557

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top