How do I delete the contents of unprotected cells only?

[Dan]

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!

[dominicb]

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

[Paul B]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!

[KFEagle]

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
>
>