Identify unprotected cells in protected sheet and clear their cont

[Guest]

Hello Gurus

I would like to create some script that dynamically identifies unprotected
cells in a worksheet and clears their contents

Thanks in advance!

 

[Bob Phillips]

Sub test()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If Not cell.Locked Then cell.ClearContents
Next cell
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

GReat Bob, thank you. I added a couple of touches to your script to make it
run a little quicker ...

Sub test()
Dim cell As Range
Application.Calculation = xlManual

For Each cell In ActiveSheet.UsedRange
If Not cell.Locked Then cell.Select
Selection.ClearContents
Next cell

Application.Calculation = xlAutomatic
End Sub

 

[Bob Phillips]

Good point. You might want to turn screenupdating off at the start and back
on at the end as well.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[NickHK]

But Bob's code did not invlolve a .Select, as there no reason for it.
Selecting will only slow thing down.

NickHK

 

[Dave Peterson]

I think you added stuff to make it faster (calculation mode stuff). But then
added stuff to make it slower--selection stuff.

Maybe dropping the .select/selection stuff would make it even quicker.

 

[NickHK]

Dave,
Swing & roundabouts..

NicHK

I think you added stuff to make it faster (calculation mode stuff). But
then
added stuff to make it slower--selection stuff.

Maybe dropping the .select/selection stuff would make it even quicker.

 

[Dave Peterson]

That must have something to do with a hit and an error???

 

[NickHK]

Dave,
http://www.phrases.org.uk/bulletin_board/28/messages/545.html

NickHK

 

[Bob Phillips]

Seeing the other guys interruptions <g>, your code is actually doubly
counter-productive.

By adding the select you slow it down, but by your code logic

If Not cell.Locked Then cell.Select
Selection.ClearContents

you not only do that but for non-locked cells you re-clear the last selected
cell (unnecessarily most of the time, and if the usedrange starts with
locked cells and you have a locked cell active, it will actually crash the
code.

Why did you add it?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)