Clear contents but leave formulas in Excel

[ojchippy]

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

 

[Bob Phillips]

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)

 

[Max]

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 D210 & 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]

The earlier suggestion was focused
more on your subject line:

Re: Clear contents but leave formulas in Excel

than anything else <g>

 

[David McRitchie]

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

 

[Max]

Thanks, David !

 

[John]

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

 

[Gord Dibben]

Excel has that function.

F5>Special>Constants


Gord Dibben MS Excel MVP

 

[Max]

to delete values and not formulas?

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

 

[John]

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

 

[David McRitchie]

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

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