PDA

View Full Version : Solved: select all unprotected cells



sunilmulay
10-31-2008, 01:32 AM
Hi there
I want a macro to delete contents of all unlocked cells in a protected sheet.
I tried the following:
Sheets("1PL").Select
Sheets("1PL").Range("J33:K999").Select
Selection.ClearContents
but because a large number of cells within the range are protected, it doesn't do it...
How do I get around this?
Thanks
S

Bob Phillips
10-31-2008, 03:25 AM
With a range that small a loop wouldn't hurt



Dim cell As Range
For Each cell In Sheets("1PL").Range("J33:K999")

If Not cell.Locked Then

cell.ClearContents
End If
Next cell

GTO
10-31-2008, 03:30 AM
...Deleted... (oops, was already nicely answered )

sunilmulay
11-01-2008, 01:16 AM
Hi there

I tried your method, but it seems to take forever to carry out the loop. Is this inevitable? I have the following code that resets all the user input:

Dim intResponse As Integer

intResponse = MsgBox("Are you sure you want to Reset Stage? This will delete all user input", vbYesNo, "Reset?")

Select Case intResponse
Case vbYes
' Process commands.
Case vbNo
Exit Sub
End Select

Application.ScreenUpdating = False


Sheets("1PL").Select
Dim cell As Range
For Each cell In Sheets("1PL").Range("E32:W999")

If Not cell.Locked Then

cell.ClearContents
End If
Next cell

Sheets("1TR").Select
For Each cell In Sheets("1TR").Range("J32:AA999")

If Not cell.Locked Then

cell.ClearContents
End If
Next cell

Call SetTimeline01

Application.ScreenUpdating = True
End Sub

Is there any way to speed it up????
Thanks
Sunil

GTO
11-01-2008, 01:30 AM
Hi Sunil,

For goodness sakes, it's notably slower because you gave an example of 1934 cells to check, when you are really checking 34,812 cells!

Try shutting calculation off during the process first, remembering to change it back to automatic when done.

Hope this helps,

Mark

sunilmulay
11-01-2008, 07:38 AM
Hi Mark
If you think you're talking to someone who knows much about VBA, you're wrong! I'm an absolute beginner, so you're going to have to help me with this!
So how do you turn calculation off and on????
Thanks!
Sunil

georgiboy
11-01-2008, 07:56 AM
Like this

Application.Calculation = xlCalculationManual

and

Application.Calculation = xlCalculationAutomatic

Hope this helps

GTO
11-01-2008, 08:33 AM
Dear Sunil,

The "...Goodness sakes...!" was meant in jest a bit, just to advise that the example can affect the answer. If I offended you, I apologize; it is/was certainly not my intent. As far as any 'answer' (barring someone abusing the forum/others) I may give, it is meant to assist.

Further - I should have included an abbreviated code snippet, my bad.

@Georgiboy:

Thank you for rectifying that :-)

Mark

mikerickson
11-01-2008, 12:11 PM
This will clear all unlocked cells on the ActiveSheet. If the sheet is already protected the .Protect .Unprotect lines can be removed.
On Error Resume Next
With ActiveSheet
.Protect
.UsedRange.Cells.Value = vbNullString
.UnProtect
End With
On Error Goto 0

mdmackillop
11-01-2008, 01:35 PM
Very neat Mike. Never thought of "partial" execution in an error situation. I'll bear that one in mind.

mikerickson
11-01-2008, 02:43 PM
I noticed it once when bulk writting to a (unknown to me) protected sheet. When the error message came up, some of the cells were changed.

I'm guessing that each locked cell throws an error (which is ignored.)

sunilmulay
11-01-2008, 05:54 PM
Mark
No offense taken at all - I just re-read my email and realised how it sounds a bit abrupt! Sorry - that's the trouble with writing as opposed to speaking to each other...
No offense at all - I was also replying in jest!
Thanks all of you for your replies. I've tried it out and it works brilliantly.
Thanks all
Sunil

GTO
11-01-2008, 07:45 PM
@sunilmulay:

Hey Sunil,

Thank you, as I wanted to make sure. Glad the suggestions worked out so well also. :thumb

@mikerickson

I just have to "second" MD's comment. I would never have thought of that! (would have been typing out the world's longest string to get it in a swipe...)

A great weekend to all,

Mark