In a large worksheet, I need to delete the data from the unprotected cells
while leaving the protected cells unchanged. Is there a quick way to do this?
Thanks for your help!

Dan, here is one way using a macro

Sub Clear_Unlocked()

Dim Cel As Range

Const Password = "123" '**Change password here, or use "" for no
password**

Application.ScreenUpdating = False

ActiveSheet.Unprotect Password:=Password

For Each Cel In ActiveSheet.UsedRange.Cells

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


"Dan" wrote in message
...
In a large worksheet, I need to delete the data from the unprotected cells
while leaving the protected cells unchanged. Is there a quick way to do
this?
Thanks for your help!

Good afternoon Dan

By unprotected cells do you mean a protected worksheet with some cells
unlocked?

If so, highlight the range containing information and run this code:

Sub test()
Count = 0
On Error Resume Next
For Each Rng In Selection
If Rng.Locked = False Then
Count = Count + 1
If Count = 1 Then Set Unlocked = Rng
If Count < 1 Then Set Unlocked = Union(Unlocked, Rng)
End If
Next Rng
Unlocked.Clear
End Sub

The routine will select all unlocked cells and clear the contents of
the selected cells.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=390937

Dominic,
I tried this method in a similar situation to Dan's. It works (from the
same worksheet), but the formatting of the unlocked cells is removed as well.
Is there a way to keep the formatting?
Also, I'm trying to run the code in a macro located on a differnet sheet.
When I run the macro, I get a "Select Method of Range Class Failed" error.
When I run the debugger the line selecting the cells I want to clear is
highlighted. It's just a simple "range("I2:AR142")" statement. I can't
figure why.
Any ideas?
Thanks
Kurt

this is the macro.....
Sub clear()

Sheets("Payroll - Collections - Pledges").Select
ActiveSheet.Unprotect Password:=Password
Range("C1:AR142").Select <-RIGHT HERE IS WHERE IT HANGS!
Count = 0
On Error Resume Next
For Each RNG In Selection
If RNG.Locked = False Then
Count = Count + 1
If Count = 1 Then Set Unlocked = RNG
If Count < 1 Then Set Unlocked = Union(Unlocked, RNG)
End If
Next RNG
Unlocked.clear <-NEED FORMATTING TO STAY!
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True

End Sub
"dominicb" wrote:


Good afternoon Dan

By unprotected cells do you mean a protected worksheet with some cells
unlocked?

If so, highlight the range containing information and run this code:

Sub test()
Count = 0
On Error Resume Next
For Each Rng In Selection
If Rng.Locked = False Then
Count = Count + 1
If Count = 1 Then Set Unlocked = Rng
If Count < 1 Then Set Unlocked = Union(Unlocked, Rng)
End If
Next Rng
Unlocked.Clear
End Sub

The routine will select all unlocked cells and clear the contents of
the selected cells.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=390937