Clear contents but leave formulas in Excel

The earlier suggestion was focused
more on your subject line:

Re: Clear contents but leave formulas in Excel

than anything else <g>
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi ojchippy and Max,

See
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

The code in the macro that clears the text constants in the inserted row(s) is
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents

Manually you can cllear the constants in a selection with
(Make a selection)
Edit, Goto (Ctrl+G), Special (button), Constants (text, numbers)
Edit, Clear, Contents
---
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

"Max" <demechanik@yahoo.com> wrote in message news:eLM%23VuzZGHA.1220@TK2MSFTNGP02.phx.gbl...
> The earlier suggestion was focused
> more on your subject line:
>
> Re: Clear contents but leave formulas in Excel
>
> than anything else <g>
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
>
>

Thanks, David !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

David McRitchie wrote:

>
> Manually you can cllear the constants in a selection with
> (Make a selection)
> Edit, Goto (Ctrl+G), Special (button), Constants (text, numbers)
> Edit, Clear, Contents
> ---
> 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
>

I made a macro of this:

Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents

I'd like to get it as a button on the page.

However... when using this if your selection has only cells with data
AND formulas in them it errors out. I guess it would have to because the
sheet wants to fill the formula.

However it would be nice to have an error catcher that simply does
nothing when that happens instead of screwing up the macro. I can reset
the macro but I want this usable by other users.

Any help with that? I don't know excell basic well enough but it would
be an:

on error goto (Or something like that)

Thanks

John

If you look at the page I referenced
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
and look for the button used with the macro, you would
see a reference to
http://www.mvps.org/dmcritchie/excel/toolbars.htm
this is a button on the toolbars, or in a menu, but the idea is
if you can make things generic, which you did, then you can
use it anywhere.

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

"John" <JOhn6328@mchsi.com> wrote in message news:eCukseYkGHA.3816@TK2MSFTNGP02.phx.gbl...
> David McRitchie wrote:
>
> >
> > Manually you can cllear the constants in a selection with
> > (Make a selection)
> > Edit, Goto (Ctrl+G), Special (button), Constants (text, numbers)
> > Edit, Clear, Contents
> > ---
> > 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
> >
>
> I made a macro of this:
>
> Selection.SpecialCells(xlCellTypeConstants, 23).Select
> Selection.ClearContents
>
> I'd like to get it as a button on the page.
>
> However... when using this if your selection has only cells with data
> AND formulas in them it errors out. I guess it would have to because the
> sheet wants to fill the formula.
>
> However it would be nice to have an error catcher that simply does
> nothing when that happens instead of screwing up the macro. I can reset
> the macro but I want this usable by other users.
>
> Any help with that? I don't know excell basic well enough but it would
> be an:
>
> on error goto (Or something like that)
>
> Thanks
>
> John