Excel protection

M

m.finch

I want to protect some cells in a sheet so that I can just select and
blank the whole sheet while keeping the formulas in some cells intact.
The problem is that, having unlocked the editable cells and protected
the worksheet, when I then select the entire sheet it won't let me just
blank the unlocked cells - it tells me that some cells are protected.
Can anyone please help.
 
G

Gord Dibben

If you want to keep ALL formulas and clear everything else......

Leave the sheet unprotected then select all cells and F5>Special>Constants.
Check or uncheck what you want to clear then OK.

With those cells selected, Edit>Clear>Contents will leave formulas intact.

If just some specific cells you could leave the sheet protected and use a
macro to select only those that are unlocked and clear contents of those.

Sub UnLocked_Cells()
Dim Cell As Range, tempR As Range, rangeToCheck As Range
Cells.Select
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If Not Cell.Locked Then
If tempR Is Nothing Then
Set tempR = Cell
Else
Set tempR = Union(tempR, Cell)
End If
End If
Next Cell
If tempR Is Nothing Then
MsgBox "There are no UnLocked cells in " & _
"the selected range."
End
End If
tempR.Select
tempR.ClearContents
End Sub


Gord Dibben Excel MVP
 
G

Gord Dibben

Another macro for clearing contents of unlocked cells.

Little less code.

Sub DeleteUnlockedCells()
Dim rngeCell As Range
Application.ScreenUpdating = False
For Each rngeCell In ActiveSheet.UsedRange.Cells
If rngeCell.Locked = False Then rngeCell.ClearContents
Next
Application.ScreenUpdating = True
End Sub


Gord
 
K

Kev Nurse

I want to protect some cells in a sheet so that I can just select and
blank the whole sheet while keeping the formulas in some cells intact.
The problem is that, having unlocked the editable cells and protected
the worksheet, when I then select the entire sheet it won't let me just
blank the unlocked cells - it tells me that some cells are protected.
Can anyone please help.
Click to expand...

Have you thought about recording a macro?
 
Top