Hello Gurus

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

Thanks in advance!

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!

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!

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!

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!

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

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

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

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!