clearing data but retaining formulas
 

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))

clearing data but retaining formulas
 

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))

clearing data but retaining formulas
 

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

Jim Thomlinson


"pm" wrote:

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))

clearing data but retaining formulas
 

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))

clearing data but retaining formulas
 

On Thu, 24 Jan 2008 08:17:08 -0800, pm wrote:

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").SpecialCe lls(xlCellTypeConstants).ClearContents


--ron

clearing data but retaining formulas
 

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))

clearing data but retaining formulas
 

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???

"Ron Rosenfeld" wrote:

On Thu, 24 Jan 2008 08:17:08 -0800, pm wrote:

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").SpecialCe lls(xlCellTypeConstants).ClearContents


--ron

clearing data but retaining formulas
 

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))

clearing data but retaining formulas
 

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

On Fri, 25 Jan 2008 14:48:01 -0800, pm wrote:

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???

"Ron Rosenfeld" wrote:

On Thu, 24 Jan 2008 08:17:08 -0800, pm wrote:

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").SpecialCe lls(xlCellTypeConstants).ClearContents


--ron

clearing data but retaining formulas
 

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


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

clearing data but retaining formulas
 

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

On Fri, 25 Jan 2008 20:16:32 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

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


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