Clearing a Range

[Ronald Cayne]

Want to clear a range of cells having formulas in the cells within the
range(eg. a1:ai1) but don't want to lose the formulas in the cleared
range. Is there a solution?

Thanks for everyone's input in helping me with problem resolutions.

Regards to all...
Ron

 

[Jim Cone]

Ron,

I think this is what you want to do?
'------------------------------------------

Sub SelectionClearTest()
' Jim Cone - San Francisco, CA - September 07, 2004
' Clears cells in selection except for formulas and their precedents.
Dim objRngPrec As Excel.Range
Dim objRngForm As Excel.Range
Dim objRngBoth As Excel.Range
Dim objRngAll As Excel.Range
Dim objRng As Excel.Range

Set objRngAll = Selection
On Error Resume Next
Set objRngForm = objRngAll.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If objRngForm Is Nothing Then
MsgBox "No Formulas in Selection. "
Set objRngAll = Nothing
Exit Sub
End If

On Error Resume Next
Set objRngPrec = objRngForm.Precedents
On Error GoTo 0
If objRngPrec Is Nothing Then
Set objRngPrec = objRngForm
End If

Set objRngBoth = Application.Union(objRngForm, objRngPrec)


For Each objRng In objRngAll.Cells
If Application.Intersect(objRng, objRngBoth) Is Nothing Then
objRng.ClearContents
End If
Next 'objRng

Set objRngPrec = Nothing
Set objRngForm = Nothing
Set objRngBoth = Nothing
Set objRngAll = Nothing
Set objRng = Nothing
End Sub
'------------------------------------------

Regards,
Jim Cone
San Francisco, CA

 

[Ronald Cayne]

Doesn't seem to work. Range not cleared when routine is run. More
specifically. I want to clear range a2:aa15 of all data and copy this
data to sheet2 a2:aa15.FORMULAS TO BE LEFT INTACT. Each time I move the
block from sheet1 to sheet2 I want to copy the entire block and paste it
in sheet2.

Sheet1 a2 should increment by 1 each time a block is moved. As each
block is moved to Sheet2 it should be offset by 1 row(ie. below the
previous row if data is in already present

Hope this is not too muddy.


In any case do you take on projects.

Ron.

 

[Jim Cone]

Ron,

Did you select the entire range, including the formulas, before running the code?
Note - any cells referenced by the formulas will not be cleared.

If you want to also clear cells referenced by the formulas then disable
this line of code: "Set objRngPrec = objRngForm.Precedents"
It is about the 19th line of code, depending on how you count lines.
(enter a single apostrophe ' at the beginning of the line)

Note - clearing cells referenced by formulas can result in #Ref error values in the formulas.

Projects? - Yes, on a fee basis - remove XXX from my email address.

Regards,
Jim Cone
San Francisco, CA
(e-mail address removed)

 

[John]

See my question on here "Debug Problem" there is an elegant solution
John