Selecting only unlocked cells to clear

[RAP]

Hello,
Can anyone give me the VB code to select all the unlocked-only cells on a
page or in a defined range, to clear them?

Thanks,
Randy

 

[Norman Jones]

Hi Randy,

I am not aware of any method of selecting unlocked cells, so I would try
something like:

''===========================>>
Public Sub ClearUnlockedCells()
Dim rcell As Range
Dim rng As Range
Dim CalcMode As Long

Set rng = ActiveSheet.UsedRange '<<======== CHANGE

With Application
.ScreenUpdating = False
CalcMode = .Calculation
.Calculation = xlManual
End With

For Each rcell In rng.Cells
If rcell.Locked = False Then
rcell.ClearContents
End If
Next rcell

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub
'<<===========================

 

[Paul B]

Randy, here is one way,

Sub Clear_Unlocked_Cells()
Dim Cel As Range
Const Password = "123" '**Change password here**
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=Password
For Each Cel In Range("A1:N25")
If Cel.Locked = False Then Cel.Formula = ""
Next
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Norman Jones]

Hi Paul,

Can you not clear unlocked cells without unprotecting / reprotecting the
worksheet?

 

[Simon Letten]

RAP

You need to test the Locked property of the cells, e.g.

For Each cell in Range
If cell.Locked = False Then
cell.ClearContents
End If
Next cell

 

[Paul B]

Norman, my bad, yes you can do it, must have been to early in the morning


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003