Identify unprotected cells in protected sheet and clear their cont
 

Hello Gurus

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

Thanks in advance!

Identify unprotected cells in protected sheet and clear their cont
 

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)

"NezRhodes" wrote in message
...
Hello Gurus

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

Thanks in advance!

Identify unprotected cells in protected sheet and clear their
 

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" wrote:

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)

"NezRhodes" wrote in message
...
Hello Gurus

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

Thanks in advance!

Identify unprotected cells in protected sheet and clear their
 

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)

"NezRhodes" wrote in message
...
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" wrote:

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)

"NezRhodes" wrote in message
...
Hello Gurus

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

Thanks in advance!

Identify unprotected cells in protected sheet and clear their
 

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

NickHK

"NezRhodes" ...
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" wrote:

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)

"NezRhodes" wrote in message
...
Hello Gurus

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

Thanks in advance!

Identify unprotected cells in protected sheet and clear their
 

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.

NezRhodes wrote:

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" wrote:

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)

"NezRhodes" wrote in message
...
Hello Gurus

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

Thanks in advance!




--

Dave Peterson

Identify unprotected cells in protected sheet and clear their
 

Dave,
Swing & roundabouts..

NicHK

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

NezRhodes wrote:

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" wrote:

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)

"NezRhodes" wrote in message
...
Hello Gurus

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

Thanks in advance!




--

Dave Peterson

Identify unprotected cells in protected sheet and clear their
 

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

NickHK wrote:

Dave,
Swing & roundabouts..

NicHK

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

NezRhodes wrote:

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" wrote:

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)

"NezRhodes" wrote in message
...
Hello Gurus

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

Thanks in advance!




--

Dave Peterson

--

Dave Peterson

Identify unprotected cells in protected sheet and clear their
 

Dave,
http://www.phrases.org.uk/bulletin_b...sages/545.html

NickHK

"Dave Peterson"
...
That must have something to do with a hit and an error???

NickHK wrote:

Dave,
Swing & roundabouts..

NicHK

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

NezRhodes wrote:

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" wrote:

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)

"NezRhodes" wrote in message
...
Hello Gurus

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

Thanks in advance!




--

Dave Peterson

--

Dave Peterson

Identify unprotected cells in protected sheet and clear their
 

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)

"NezRhodes" wrote in message
...
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" wrote:

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)

"NezRhodes" wrote in message
...
Hello Gurus

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

Thanks in advance!