Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
newsgal
 
Posts: n/a
Default Excel: Clear all cell entries EXCEPT formulas?

Excel:
Is there any way to clear all cell entries EXCEPT formulas so that a
spreadsheet that needs to have new entries every month can be cleared easily
for the new month without having to work around the formulas when clearing
the cells?
  #2   Report Post  
Paul B
 
Posts: n/a
Default

Newsgal, edit, go to, special, constants, OK, delete, or how about saving
the file as a template and open a new copy each , month?
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"newsgal" wrote in message
...
Excel:
Is there any way to clear all cell entries EXCEPT formulas so that a
spreadsheet that needs to have new entries every month can be cleared
easily
for the new month without having to work around the formulas when clearing
the cells?



  #3   Report Post  
Jason Morin
 
Posts: n/a
Default

Select the range of cells and run this macro:

Sub ClearAllSaveFormulas()
Dim cell As Range
For Each cell In Selection
With cell
If Not .HasFormula Then
.ClearContents
End If
End With
Next
End Sub

---
To run, press ALT+F11, go to Insert Module, and paste in the code above.
Press ALT+Q. Now select your range of cells and go to Tools Macro Macros
and run the macro.

HTH
Jason
Atlanta, GA


"newsgal" wrote:

Excel:
Is there any way to clear all cell entries EXCEPT formulas so that a
spreadsheet that needs to have new entries every month can be cleared easily
for the new month without having to work around the formulas when clearing
the cells?

  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

I bet you have constants that you want to keep--row and column headers,
instructions/descriptions for data entry???

If that's the case, select your range to clear and give it a nice name.
(Insert|name|define)

Then whenever you want to clear that range,
just hit Edit|Goto (or F5 or ctrl-g)
select that name (or type it in)
and hit the delete key.



newsgal wrote:

Excel:
Is there any way to clear all cell entries EXCEPT formulas so that a
spreadsheet that needs to have new entries every month can be cleared easily
for the new month without having to work around the formulas when clearing
the cells?


--

Dave Peterson
  #5   Report Post  
David McRitchie
 
Posts: n/a
Default

Hi newgal,
You can also use formulas for constants, for that matter.

When I was inserting rows, the clearing of constants was
my original stumbling block, I had found the rest of what I needed.
You can see an example in the first macro at
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
The purpose of the macro was to insert rows copying formulas,
the soluton was to insert rows, copying entire rows, then deleting
the constants. The subroutine has several interesting things in it.

That web page also has variations of the macro you were already
given triggered by double-click.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave Peterson" wrote in message ...
I bet you have constants that you want to keep--row and column headers,
instructions/descriptions for data entry???

If that's the case, select your range to clear and give it a nice name.
(Insert|name|define)

Then whenever you want to clear that range,
just hit Edit|Goto (or F5 or ctrl-g)
select that name (or type it in)
and hit the delete key.



newsgal wrote:

Excel:
Is there any way to clear all cell entries EXCEPT formulas so that a
spreadsheet that needs to have new entries every month can be cleared easily
for the new month without having to work around the formulas when clearing
the cells?


--

Dave Peterson





  #6   Report Post  
Posted to microsoft.public.excel.misc
dvonj
 
Posts: n/a
Default Excel: Clear all cell entries EXCEPT formulas?

This is almost exactly what I am looking for except...
I am using a Personal Budget spreadsheet that is perdesgined from the
Template Download site.
http://office.microsoft.com/en-us/te...CT011377171033.
I have modified it to work the way I want it to work. I want to create a
button that upon clicking will clear all cells containing entries and
calculations so they can receive new data. The macro by Jason is cool but how
can I make it work with a button? Also the sheet has several cell ranges that
would need to be cleared without clearing the formulas the cells contain. Any
ideas on how I can make this work?

"Jason Morin" wrote:

Select the range of cells and run this macro:

Sub ClearAllSaveFormulas()
Dim cell As Range
For Each cell In Selection
With cell
If Not .HasFormula Then
.ClearContents
End If
End With
Next
End Sub

---
To run, press ALT+F11, go to Insert Module, and paste in the code above.
Press ALT+Q. Now select your range of cells and go to Tools Macro Macros
and run the macro.

HTH
Jason
Atlanta, GA


"newsgal" wrote:

Excel:
Is there any way to clear all cell entries EXCEPT formulas so that a
spreadsheet that needs to have new entries every month can be cleared easily
for the new month without having to work around the formulas when clearing
the cells?

  #7   Report Post  
Posted to microsoft.public.excel.misc
dvonj
 
Posts: n/a
Default Excel: Clear all cell entries EXCEPT formulas?



"dvonj" wrote:

This is almost exactly what I am looking for except...
I am using a Personal Budget spreadsheet that is perdesgined from the
Template Download site.
http://office.microsoft.com/en-us/te...CT011377171033.
I have modified it to work the way I want it to work. I want to create a
button that upon clicking will clear all cells containing entries and
calculations so they can receive new data. The macro by Jason is cool but how
can I make it work with a button? Also the sheet has several cell ranges that
would need to be cleared without clearing the formulas the cells contain. Any
ideas on how I can make this work?

"Jason Morin" wrote:

Select the range of cells and run this macro:

Sub ClearAllSaveFormulas()
Dim cell As Range
For Each cell In Selection
With cell
If Not .HasFormula Then
.ClearContents
End If
End With
Next
End Sub

---
To run, press ALT+F11, go to Insert Module, and paste in the code above.
Press ALT+Q. Now select your range of cells and go to Tools Macro Macros
and run the macro.

HTH
Jason
Atlanta, GA


"newsgal" wrote:

Excel:
Is there any way to clear all cell entries EXCEPT formulas so that a
spreadsheet that needs to have new entries every month can be cleared easily
for the new month without having to work around the formulas when clearing
the cells?

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
How do I get top cell alignment in excel 2003 marty53 Excel Discussion (Misc queries) 4 April 2nd 05 05:05 PM
How do I fix Text entries in Excel that get cut off? JLaw Excel Discussion (Misc queries) 2 March 26th 05 01:28 PM
Can I copy a combo box in Excel 2002 with a relative cell link? Bozo Excel Discussion (Misc queries) 1 February 17th 05 03:05 AM
In MS Excel, how do I fill in a column with the same cell from se. krempin Excel Worksheet Functions 2 February 9th 05 09:43 PM
How Can I make a cell flash in Excel monir Excel Discussion (Misc queries) 0 February 4th 05 04:41 AM


All times are GMT +1. The time now is 03:11 PM.

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

About Us

"It's about Microsoft Excel"