Delete data and leave formulas in place with macro

[teddavenport]

I need a macro (or Excel function) that will delete the data from a spreadsheet but leave the formulas intact. I don't want to hide the data, I want to delete the data so I can use the same formulas for adding other data. "Clear All" won't leave the formulas intact. I also want to leave the field names intact. I only want to delete the data (numbers). Thanks in advance for you help

[Jay Petrulis]

Hi,

Try
----------------
Sub test()
Dim DataRng As Range, UsedCell As Range

Set DataRng = ActiveSheet.UsedRange

For Each UsedCell In DataRng
If Not UsedCell.HasFormula Then UsedCell.ClearContents
Next UsedCell

End Sub
-----------------

Bye,
Jay

[teddavenport]

Thanks Jay for the reply. Although I am not proficient at programming language, it appears your foumula will delete my field names which I don't want to do. Is this the case.? Agains thanks.

[Tom Schreiner]

This macro will clear numeric
Values only.

Sub ClearRange()
For Each c In UsedRange
If IsNumeric(c) And _
Not c.HasFormula Then _
c.ClearContents
Next
End Sub

Tom

[ This Message was edited by: TsTom on 2002-04-25 06:57 ]

[Ricky Morris]

Another way without using VBA

Select Edit > Goto from the menu, click Special, click constant, choose the data type: Numbers, Text, etc. Click OK. Press Delete.

[Jay Petrulis]

On 2002-04-25 06:47, teddavenport wrote:
Thanks Jay for the reply. Although I am not proficient at programming language, it appears your foumula will delete my field names which I don't want to do. Is this the case.? Agains thanks.

Sorry, I didn't understand what you meant by field names. I thought that meant named ranges, which wouldn't be deleted, rather than cells containing text.

The following is my correction, which matches what TsTom just supplied, but with the full sub. Also, Ricky's response will work, too, I believe.

Sub test()
Dim DataRng As Range, UsedCell As Range

Set DataRng = ActiveSheet.UsedRange

For Each UsedCell In DataRng
If IsNumeric(UsedCell) And _
Not UsedCell.HasFormula Then UsedCell.ClearContents
Next UsedCell

End Sub

Bye,
Jay

[teddavenport]

Hey Guys,
Thanks. Since I couldn't figure out how to save the routines and run them, I chose Ricky's method and it works perfectly. I appreciate all the help.
Mucho gracious.