Worksheet clearing Macros?
 

Does anyone know how to write a Macro that, when activated, would clear all
the data NOT in a protected cell and leave the protected formulas alone? Or,
can this be done with regular Excel functions?

While we're at it, is it possible to use Conditional Formatting to clear
cell contents, again, leaving formulas in tact?

Thank you, everybody

Arlen

Hi Arlen

Sub test()
On Error Resume Next
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants ).ClearContents
End Sub

Manual you can do this

F5
Special
Constants
Ok
Delete

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Arlen" wrote in message ...
Does anyone know how to write a Macro that, when activated, would clear all
the data NOT in a protected cell and leave the protected formulas alone? Or,
can this be done with regular Excel functions?

While we're at it, is it possible to use Conditional Formatting to clear
cell contents, again, leaving formulas in tact?

Thank you, everybody

Arlen

Arlen

To address your second question....

No. CF cannot be used to clear cell contents.


Gord Dibben Excel MVP

On Wed, 26 Jan 2005 20:31:35 +0100, "Ron de Bruin"
wrote:

Hi Arlen

Sub test()
On Error Resume Next
ActiveSheet.Cells.SpecialCells(xlCellTypeConstant s).ClearContents
End Sub

Manual you can do this

F5
Special
Constants
Ok
Delete

I read it slightly differently.

Clean up any constant cell that is unlocked (no formulas and no protected cells
will change).

Option Explicit
Sub test2()
Dim myCell As Range
With Worksheets("sheet1")
For Each myCell In .UsedRange.Cells
If myCell.Locked = True _
Or myCell.HasFormula = True Then
'do nothing
Else
myCell.ClearContents
End If
Next myCell
End With
End Sub



Arlen wrote:

Does anyone know how to write a Macro that, when activated, would clear all
the data NOT in a protected cell and leave the protected formulas alone? Or,
can this be done with regular Excel functions?

While we're at it, is it possible to use Conditional Formatting to clear
cell contents, again, leaving formulas in tact?

Thank you, everybody

Arlen

--

Dave Peterson

The OP is using this now (private mail)

Sub test()
With Sheets("sheet1")
.Unprotect "ron"
On Error Resume Next
.Range("A2:G100").Cells.SpecialCells(xlCellTypeCon stants).ClearContents
.Protect "ron"
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Dave Peterson" wrote in message ...
I read it slightly differently.

Clean up any constant cell that is unlocked (no formulas and no protected cells
will change).

Option Explicit
Sub test2()
Dim myCell As Range
With Worksheets("sheet1")
For Each myCell In .UsedRange.Cells
If myCell.Locked = True _
Or myCell.HasFormula = True Then
'do nothing
Else
myCell.ClearContents
End If
Next myCell
End With
End Sub



Arlen wrote:

Does anyone know how to write a Macro that, when activated, would clear all
the data NOT in a protected cell and leave the protected formulas alone? Or,
can this be done with regular Excel functions?

While we're at it, is it possible to use Conditional Formatting to clear
cell contents, again, leaving formulas in tact?

Thank you, everybody

Arlen

--

Dave Peterson