[SOLVED] Excel should let me clear cell contents without clearing formulas

Excel allows you to clear cell contents but it also clears formulas. We
sometimes need to clear cells to produce mockup copies of financial
statements that need to be blank in order to enter new numbers from year to
year. Too much time has to be spent in determing which cells have formulas
(which we want to keep) and which do not. We need another option to
accomplish this. We use Microsoft Office Excel 2003.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

Use this

Select your data
F5
Special>Constants
OK
Delete

See also the VBA help for SpecialCells


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


"BJM" <[email protected]> wrote in message news:[email protected]...
> Excel allows you to clear cell contents but it also clears formulas. We
> sometimes need to clear cells to produce mockup copies of financial
> statements that need to be blank in order to enter new numbers from year to
> year. Too much time has to be spent in determing which cells have formulas
> (which we want to keep) and which do not. We need another option to
> accomplish this. We use Microsoft Office Excel 2003.
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...el.programming

You already have it.

Select the area you want to analyze and do

Edit=>goto Special and select constants (add further restrictions if you
just want to clear numbers or just text or just text and numbers)

then do Edit clear contents.

--
Regards,
Tom Ogilvy


"BJM" <[email protected]> wrote in message
news:[email protected]...
> Excel allows you to clear cell contents but it also clears formulas. We
> sometimes need to clear cells to produce mockup copies of financial
> statements that need to be blank in order to enter new numbers from year
to
> year. Too much time has to be spent in determing which cells have
formulas
> (which we want to keep) and which do not. We need another option to
> accomplish this. We use Microsoft Office Excel 2003.
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow
this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
>
http://www.microsoft.com/office/comm...el.programming

Here's a thought too
After clearing entries save it as a template and you always have a clean
sheel to start from.
Lou

"Tom Ogilvy" wrote:

> You already have it.
>
> Select the area you want to analyze and do
>
> Edit=>goto Special and select constants (add further restrictions if you
> just want to clear numbers or just text or just text and numbers)
>
> then do Edit clear contents.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "BJM" <[email protected]> wrote in message
> news:[email protected]...
> > Excel allows you to clear cell contents but it also clears formulas. We
> > sometimes need to clear cells to produce mockup copies of financial
> > statements that need to be blank in order to enter new numbers from year
> to
> > year. Too much time has to be spent in determing which cells have
> formulas
> > (which we want to keep) and which do not. We need another option to
> > accomplish this. We use Microsoft Office Excel 2003.
> >
> > ----------------
> > This post is a suggestion for Microsoft, and Microsoft responds to the
> > suggestions with the most votes. To vote for this suggestion, click the "I
> > Agree" button in the message pane. If you do not see the button, follow
> this
> > link to open the suggestion in the Microsoft Web-based Newsreader and then
> > click "I Agree" in the message pane.
> >
> >
> http://www.microsoft.com/office/comm...el.programming
>
>
>

Since this is the programming newsgroup the posting about Excel not providing
functionality is out of place, becase you can certainly program this action.

It would be probably be nice to have a "Clear Constants" item on the the context
menu just like there is a "Clear Contents" item and that like "Clear Contents" will
only affect one cell if only cell is selected.

I've not seen Microsoft add additional function to an existing version, so it would be
a long time before you would see any change, meanwhile, ...

Both Excel and VBA allow you to clear content of non formulas, and since
this is the programming group, you can make up a toolbar button, menu, or context
menu (right click menu) to make it easier with a macro...

Excel::
Select an area
Ctrl+G (Edit, GoTo), Special, Constants (with Numbers, Text, Logicals, Errors)
Del key (or Edit, Clear Contents)

VBA:
Selection.EntireRow.SpecialCells(xlConstants).ClearContents
or
Selection.SpecialCells(xlConstants).ClearContents

Sub Clear_Constants() 'D.McRitchie 2005-11-26 rightclick.htm - insrtrow.htm
Dim rng As Range 'prevent expansion of a single cell selection
Set rng = Intersect(Selection, Selection.SpecialCells(xlConstants))
If rng Is Nothing Then
MsgBox "No constants in selection area(s) -- no removal"
Else
rng.ClearContents
End If
End Sub

Installation for use in context menu (rightclick) requires additions to
cell, column, and row CommandBar controls.

More information in http://www.mvps.org/dmcritchie/excel...lear_constants
More information in http://www.mvps.org/dmcritchie/excel/rightclick.htm
---
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

"BJM" <[email protected]> wrote in message news:[email protected]...
> Excel allows you to clear cell contents but it also clears formulas. We
> sometimes need to clear cells to produce mockup copies of financial
> statements that need to be blank in order to enter new numbers from year to
> year. Too much time has to be spent in determing which cells have formulas
> (which we want to keep) and which do not. We need another option to
> accomplish this. We use Microsoft Office Excel 2003.
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
>
http://www.microsoft.com/office/comm...el.programming

date in comment s/b 2005-11-19