delete values in several cells without deleting the formulas

D

dranreb

I wanted to clear the values in a spreadsheet without deleting the formulas
or formatting in the spreadsheet. For example: A budget sheet made for every
year, the formulas and formatting remains the same, only the values change.
I tried paste special formulas....in this case, if values are present, they
get copied too.
 
F

Frank Kabel

Hi
you can't delete the 'values' from a formula. The formula will always
calculate its result
 
R

Ron de Bruin

Hi dranreb

Make a copy of the worksheet
F5>Special
Constants
OK
Delete the selection now
 
J

John Mansfield

You can also delete the values via code if needed:

Sub DeleteNumbers()
On Error Resume Next
Cells.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
End Sub

Be careful with this. Since dates are stored as numbers, it will delete
them too.
 
D

Dave Peterson

If you're careful with your selection, you can clear the constants.

But make sure you avoid (don't select) headers/descriptions/instructions that
you want to keep.

You can select a range, ctrl-click select another range, etc to avoid the
constants that should be kept.
 
Top