Excel: Clear all cell entries EXCEPT formulas?

[Guest]

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?

 

[Paul B]

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

 

[Guest]

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

 

[Dave Peterson]

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.

 

[David McRitchie]

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.

 

[Guest]

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/templates/TC010233411033.aspx?CategoryID=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?

 

[Guest]

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/templates/TC010233411033.aspx?CategoryID=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?