Delete contents unlocked cells
 

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

Rob

Delete contents unlocked cells
 

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:

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

Rob

Delete contents unlocked cells
 

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

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

Rob

Delete contents unlocked cells
 

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:

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

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

Rob

Delete contents unlocked cells
 

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

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

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

Rob

Delete contents unlocked cells
 

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:

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

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

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

Rob

Delete contents unlocked cells
 

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:

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:

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

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

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

Rob

Delete contents unlocked cells
 

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

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:

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

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

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

Rob

Delete contents unlocked cells
 

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

Stefi

€žRobN€� ezt Ã*rta:

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

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:

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

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

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

Rob

Delete contents unlocked cells
 

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

"Stefi" wrote in message
...
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
).

Stefi

"RobN" ezt írta:

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

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:

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

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

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

Rob