delete all but protected cells

K

Kingtriotn

Hello,
I am trying to put together a macro to delete all but the protected
cells. Nothing I try seems to work. Has anyone done this before? It
is easy for me to turn protection on and off with a macro so shouldnt
it also be easy to delete the content of all non protected cells as
well?
Thanks in advance
Kingtriton
 
G

Gord Dibben

King

Sub UnLocked_Cells()
Dim cell As Range, tempR As Range, rangeToCheck As Range
'rotate through all cells in the selection
For Each cell In Intersect(Selection, 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
'select qualifying cells
tempR.Select
Selection.ClearContents
End Sub

Gord Dibben Excel MVP
 
G

Gord Dibben

King

Macro works for me on a protected sheet.

Hit CRTL + A to select all cells then run it.

Clears the contents of all "unlocked" cells.

Gord Dibben Excel MVP
 
G

Gord Dibben

Tested in XL 97 and XL 2002

Gord

King

Macro works for me on a protected sheet.

Hit CRTL + A to select all cells then run it.

Clears the contents of all "unlocked" cells.

Gord Dibben Excel MVP
Click to expand...
 
Top