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?

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" <newsgal@discussions.microsoft.com> wrote in message
news:0178E9F5-A9CC-422F-A651-53525C61AE37@microsoft.com...
> 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?

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?

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?

"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?

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

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" <ec35720@netscapeXSPAM.com> wrote in message news:42766087.8CD69EB1@netscapeXSPAM.com...
> 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