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
...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
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
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
@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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.