Clear contents but leave formulas in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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.
 
The earlier suggestion was focused
more on your subject line:

Re: Clear contents but leave formulas in Excel

than anything else <g>

---
 
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 said:
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.
Click to expand...
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 said:
to delete values and not formulas?
Click to expand...
:
Excel has that function.
F5>Special>Constants
Click to expand...

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.

---
 
David said:
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
Click to expand...

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