delete values in several cells without deleting the formulas
 

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.

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

--
Regards
Frank Kabel
Frankfurt, Germany

"dranreb" schrieb im Newsbeitrag
...
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.

Hi dranreb

Make a copy of the worksheet
F5Special
Constants
OK
Delete the selection now

--
Regards Ron de Bruin
http://www.rondebruin.nl


"dranreb" wrote in message ...
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.

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.

"Ron de Bruin" wrote:

Hi dranreb

Make a copy of the worksheet
F5Special
Constants
OK
Delete the selection now

--
Regards Ron de Bruin
http://www.rondebruin.nl


"dranreb" wrote in message ...
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.

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.

dranreb wrote:

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.

--

Dave Peterson