Delete contents unlocked cells

[RobN]

Could someone please provide me with some code to clear contents of all
unlocked cells in a worksheet?

Rob

 

[Stefi]

Try this:
Sub delunlocked()
Dim aktter As Range, acell As Range
Set aktter = Selection.CurrentRegion
For Each acell In aktter
If Not acell.Locked Then acell.ClearContents
Next acell
End Sub

Regards,
Stefi


„RobN†ezt írta:

 

[RobN]

Thanks Stefi,

But this comes up with a runtime error 1004.

Do I need to select a range or something or will this do the whole active
sheet?

Rob

 

[Stefi]

You didn't post which line causes the error! Anyway try this version, it
will work on the whole active sheet:

Sub delunlocked()
Dim aktter As Range, acell As Range
Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Activate
Set aktter = Range("A1", ActiveCell)
For Each acell In aktter
If Not acell.Locked Then acell.ClearContents
Next acell
End Sub

Which Excel version do you use?

Stefi


„RobN†ezt írta:

 

[RobN]

Thanks Stefi,
That worked a treat!
I use vs 2007 (and 2002 for my important files, he says cynically!)

BTW, what made you call one of the dims aktter?

Rob

 

[Stefi]

You are welcome! Thanks for the feedback!
Sorry for "aktter", I forgot that it means nothing to non-Hungarian
speakers, it stands for "aktuális terület" which means currently used range.

Stefi

„RobN†ezt írta:

 

[Stefi]

I found a simpler and shorter code (without needing "aktter"):

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

Stefi

„Stefi†ezt írta:

 

[RobN]

Thanks again Stefi!
It works fine except.....
For some strange reason I get the runtime error 1004 message, but only after
it has done about half the selected range. I amended the code a bit as
shown below, to see on which cell the error occurs. It's the same cell each
time but this cell is no different to most of the other cells immediately
surrounding it. (That is, it is formatted the same. I copied and pasted the
previous cell on which the code works to the offending cell.) In fact the
workbook has the same sheet repeated 12 times and the error occurs in the
same cell on each sheet!
Any ideas?

Sub delunlocked()
Dim acell As Range
Set aRange = Range("A1:J75")
For Each acell In aRange
If Not acell.Locked Then acell.ClearContents
acell.Select
Next acell
End Sub

Rob

 

[Stefi]

I have no idea about the reason of the error...
I would add

Dim aRange As Range

to your code, but it can't be the real reason...

If you send me a copy of the workbook, I'll give it a try
([email protected]).

Stefi

„RobN†ezt írta:

 

[RobN]

Thanks Stefi,

Just to keep this group informed, your code below worked perfectly after
discovering merged cells in the sheet.


Sub delunlocked()

Dim aRange As Range, acell As Range

Set aRange = Range("A1:J75")

For Each acell In aRange

If Not acell.Locked Then acell.MergeArea.ClearContents

Next acell

End Sub

Rob