Clear only unprotected cells

[pilot]

I need a macro to clear unprotected cells on a protected sheet. If it matters, the cells may contain values or text. Also, this is in Excel2002 which offers lots more options with respect to sheet protection. I tried creating a named range that includes every cell in the print area but once sheet protection is activated, the entire range can't be selected. I would even accept clearing every unprotected cell on the sheet. I'll just bet there's some simple code to do this.

[Derek]

Hi there

You could try something like this:

For Each cell In Range("B4:J22")
If cell.Locked = False Then
cell.ClearContents
End If
Next
End Sub

Change the range to your range
(works in Excel 2000)

regards
Derek

[Tom Schreiner]

Hi
This will clear unlocked cells rather the
worksheet is protected or not...
Note:
If you do not want to wait around,
change this Line:

For Each c In ActiveSheet.Cells

to a bit smaller range such as:

For Each c In range.("A1:Z1000")

Sub ClearCells()
Dim c
For Each c In ActiveSheet.Cells
If c.Locked = False Then _
c.ClearContents
Next
End Sub

You may also Unprotect a sheet via code at
the beginning of you procedure,
run your code, and then protect it again.

ActiveSheet.Unprotect password:="YourPassword"
'code here
ActiveSheet.Protect password:="YourPassword"

The password is optional...

Tom

[Tom Schreiner]

Didn't mean to repeat you there Derek...

Tom

[pilot]

Thanks guys, this will do it.