Excel Formula

[AD]

Is there a formula that will automatically clear ALL unprotected cells? i
have a spreadsheet and in lieu of erasing all the unprotected cells I am
hoping there is a formula that with just one click it will erase all of the
contents of the unproteced cells.

thanks for your help

 

[Gary''s Student]

Try this small macro:

Sub ClearUm()
Set r = ActiveSheet.UsedRange
For Each rr In r
If rr.Locked = True Then
Else
rr.Clear
End If
Next
End Sub

 

[Dave Peterson]

Formulas can't touch other cells. About the only thing that they can do is
return values to the cells that contain that formula.

But you can use a macro.

Do you really mean all the unlocked cells on a protected worksheet?

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
End With

If myRng Is Nothing Then
'no constants to clear
Else
For Each myCell In myRng.Cells
If myCell.Locked = False Then
myCell.ClearContents
End If
Next myCell
End If

End Sub

I limited my range to check to just the constants--no formulas. I figured that
if you have a formula on that worksheet, then it should be saved--even if the
cell isn't locked.