Worksheet clearing Macros?

A

Arlen

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
 
R

Ron de Bruin

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
 
G

Gord Dibben

Arlen

To address your second question....

No. CF cannot be used to clear cell contents.


Gord Dibben Excel MVP
 
D

Dave Peterson

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
 
R

Ron de Bruin

The OP is using this now (private mail)

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