Excel Formula
 

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

Excel Formula
 

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

--
Gary''s Student - gsnu200907


"AD" wrote:

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

Excel Formula
 

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.



AD wrote:

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

--

Dave Peterson