Thanks Rick,

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

"Rick Rothstein (MVP - VB)" wrote:

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

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

Rick


"pm" wrote in message
...
Hi Rick,

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

"Rick Rothstein (MVP - VB)" wrote:

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


"pm" wrote in message
...
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))