clearing data but retaining formulas

[pm]

I have a macro that clears the contents of a range of cells; however, I would
like to keep the formulas in certain cells, just clear the data....how can I
do this? Thanks.


'Clear User Lists on Control Sheet
Sheets("Control").Select
Range("A8:E65536").Select
Selection.ClearContents
Range("A5").Select
Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1))

 

[Rick Rothstein \(MVP - VB\)]

Instead of ClearContents, you can just assign the empty string "" to the
range. Here is your code modified to do this and with all those unnecessary
Select/Selections removed (and a With/EndWith block to make it more
readable)...

With Sheets("Resource Details")
.Range("A8:E65536") = ""
.Cells(5, 1) = UCase(.Cells(5, 1))
End With

Rick

 

[Jim Thomlinson]

On Error Resume Next
With Sheets("Control")
.Range(.Range("A8"), .Cells(Rows.Count, _
"E")).SpecialCells(xlCellTypeConstants).ClearContents
End With
On Error Goto 0

 

[pm]

Hi Rick,

I tried running this macro with the empty string "", but it still erased my
formula..any other suggestions?
Thanks in advance.

 

[Ron Rosenfeld]

I have a macro that clears the contents of a range of cells; however, I would
like to keep the formulas in certain cells, just clear the data....how can I
do this? Thanks.


'Clear User Lists on Control Sheet
Sheets("Control").Select
Range("A8:E65536").Select
Selection.ClearContents
Range("A5").Select
Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1))

Worksheets("Control").Range("A8:E65536").SpecialCells(xlCellTypeConstants).ClearContents


--ron

 

[Rick Rothstein \(MVP - VB\)]

Sorry, I missed the "formula" part of your question. This should work...

With Sheets("Resource Details")
.Range("A8:E65536").SpecialCells(xlCellTypeConstants).ClearContents
.Cells(5, 1) = UCase(.Cells(5, 1))
End With

Rick

 

[pm]

Hey Ron,
That worked...now I'm getting an #N/A in my formula fields. So, I need an
IF statement to blank the error???

 

[pm]

Thanks Rick,

How do I get rid of the error N/A in the formula fields, please.....Thanks.

 

[Gord Dibben]

Incorporate this into your code or just run it in tandem with your other code.

Sub NATrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISNA*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

Normally, you would hand that in the formula itself by checking for the
error with an IF function call and returning "" if there is an error
otherwise return your formula. See Gord Dibben's post (located under Ron
Rosenfeld message) for an addition to the macro that will handle modifying
your formula for you (but keep in mind the idea for future formulas you
write).

Rick

 

[Gord Dibben]

I left out the part that should have reminded OP that his formulas should
contain the ISNA trap long before he reaches the stage he is at.

By habit all formulas should be error-trapped for whatever type of error is
expected.

Thanks for pointing that out Rick.


Gord