How to delete here and there in a worksheet....

[Zadig Galbaras]

Hi...

How can I delete the contains of all unlocked cells in a worksheet in one
sweep?

I have this worksheet with 6 * 13 * 33 cells scattered around the entire
spreadsheet, divided by an unknown number of hidden and locked cells
containing formulas vital to the entire spreadsheet.

I have hidden the locked cells.
I have removed the mark letting me choose locked cells.
I cannot mark the whole spreadsheet in order to hit delete.

--

Regards
Zadig Galbaras
(nick)
-----

 

[Gord Dibben]

Sub UnLocked_Cells()
Dim cell As Range, tempR As Range, rangeToCheck As Range
For Each cell In ActiveSheet.UsedRange
If Not cell.Locked Then
'add to tempR if unprotected
If tempR Is Nothing Then
'do it this way for the first unprotected cell
Set tempR = cell
Else
'add subsequent cells this way.
Set tempR = Union(tempR, cell)
End If
End If
Next cell
'do this upon completion of the For.....Next checking
If tempR Is Nothing Then
MsgBox "There are no UnLocked cells in " & _
"the selected range."
End
End If
tempR.ClearContents
End Sub


Gord Dibben MS Excel MVP