Clear contents but leave formulas in Excel

I am trying to copy data from a table that has formulas assigned to it so
that when I enter new data in the table the formulas still work. When I copy
the data all I want to copy is the raw data, No formats, cell names etc. Also
I was hope to do all of this by a macro but can't get it to work correctly.
Please help

Look up PasteSpecial in VBA help. There is a formulas option in the type
argument.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ojchippy" <[email protected]> wrote in message
news:[email protected]...
> I am trying to copy data from a table that has formulas assigned to it so
> that when I enter new data in the table the formulas still work. When I
copy
> the data all I want to copy is the raw data, No formats, cell names etc.
Also
> I was hope to do all of this by a macro but can't get it to work
correctly.
> Please help

Here's one play to try out ..

Assume that in a sheet: X,
the data input ranges are B2:C10 and E2:F10
(there could be formulas in D2:D10 & G2:G10.
and labels in row1, etc)

We'll quickly create a defined range to refer to the 2 input ranges
Click Insert > Name > Define
Make the settings as
Names in workbook: MyRange
Refers to: =X!$B$2:$C$10,X!$E$2:$F$10
Click OK

Now put the sub below in a regular module

Press Alt + F11 to go to VBE
Click Insert > Module
Copy & paste the sub into the code window
Press Alt + Q to exit and get back to Excel

'--------
Sub Clear_MyRange()
Application.Goto Reference:="MyRange"
Selection.ClearContents
End Sub
'--------

In Excel, in the sheet: X

Draw a forms button near the defined range
(if reqd, activate the forms toolbar via View > Toolbars > Forms)
Assign the sub "Clear_MyRange" to the button
(label the button to taste)

Now, whenever we want to clear the data input ranges,
we can click the button.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ojchippy" wrote:
> I am trying to copy data from a table that has formulas assigned to it so
> that when I enter new data in the table the formulas still work. When I copy
> the data all I want to copy is the raw data, No formats, cell names etc. Also
> I was hope to do all of this by a macro but can't get it to work correctly.
> Please help

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" <[email protected]> wrote in message news:eLM%[email protected]...
> 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
---

Max wrote:
> Here's one play to try out ..
>
> Assume that in a sheet: X,
> the data input ranges are B2:C10 and E2:F10
> (there could be formulas in D2:D10 & G2:G10.
> and labels in row1, etc)
>
> We'll quickly create a defined range to refer to the 2 input ranges
> Click Insert > Name > Define
> Make the settings as
> Names in workbook: MyRange
> Refers to: =X!$B$2:$C$10,X!$E$2:$F$10
> Click OK
>
> Now put the sub below in a regular module
>
> Press Alt + F11 to go to VBE
> Click Insert > Module
> Copy & paste the sub into the code window
> Press Alt + Q to exit and get back to Excel
>
> '--------
> Sub Clear_MyRange()
> Application.Goto Reference:="MyRange"
> Selection.ClearContents
> End Sub
> '--------
>
> In Excel, in the sheet: X
>
> Draw a forms button near the defined range
> (if reqd, activate the forms toolbar via View > Toolbars > Forms)
> Assign the sub "Clear_MyRange" to the button
> (label the button to taste)
>
> Now, whenever we want to clear the data input ranges,
> we can click the button.
This is the most idiotic thing I've ever heard of. Why doesn't excel
just have a function to delete values and not formulas? Like, hasn't
this subject ever come up before? Dhuuuuuuu
John

Excel has that function.

F5>Special>Constants


Gord Dibben MS Excel MVP

On Mon, 12 Jun 2006 19:26:59 -0500, John <[email protected]> wrote:

>This is the most idiotic thing I've ever heard of. Why doesn't excel
>just have a function to delete values and not formulas? Like, hasn't
>this subject ever come up before? Dhuuuuuuu
>John

>> John <[email protected]> wrote:
> > .. Why doesn't excel just have a function
> to delete values and not formulas?

> "Gord Dibben" wrote:
> Excel has that function.
> F5>Special>Constants

Thanks, Gord !

John: Trouble usually encountered is that: F5>Special>Constants
makes no distinction between say, col labels (or text notes in cells, etc)
which are to be preserved, and input data ranges (for downstream calcs) to be
cleared / reset. That's why the little extra effort suggested earlier to
define input data ranges for subsequent reset/clearing later isn't quite as
idiotic as it may have seemed to you.
--
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" <[email protected]> wrote in message news:[email protected]...
> 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