Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 8th 04, 09:19 PM
dranreb
 
Posts: n/a
Default 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.

  #2   Report Post  
Old December 8th 04, 09:27 PM
Frank Kabel
 
Posts: n/a
Default

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.


  #3   Report Post  
Old December 8th 04, 09:30 PM
Ron de Bruin
 
Posts: n/a
Default

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.



  #4   Report Post  
Old December 8th 04, 10:29 PM
John Mansfield
 
Posts: n/a
Default

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.




  #5   Report Post  
Old December 9th 04, 01:15 AM
Dave Peterson
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Second serie doesn't use X-as values JackRnl Charts and Charting in Excel 1 January 20th 05 01:04 AM
Format cells to display truncated values? Mike Excel Discussion (Misc queries) 2 December 7th 04 10:13 PM
How do I view formulas by values not by which cells they use to c. excelhelp Excel Discussion (Misc queries) 1 December 6th 04 12:30 AM
Protected cells -automatically format to a different color Fred Evans Excel Discussion (Misc queries) 9 December 3rd 04 12:59 PM
How to add a button to restore all altered cells original values? Dawnybros Excel Discussion (Misc queries) 2 December 2nd 04 04:35 PM


All times are GMT +1. The time now is 11:33 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017