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

Rob

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

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" <(E-Mail Removed)> wrote in message
newsE0EA4CB-85FA-4C78-A66A-(E-Mail Removed)...
> 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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> newsE0EA4CB-85FA-4C78-A66A-(E-Mail Removed)...
> > 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
> >>
> >>
> >>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:06477886-AD14-42A4-B7DB-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> newsE0EA4CB-85FA-4C78-A66A-(E-Mail Removed)...
>> > 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
>> >>
>> >>
>> >>
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:06477886-AD14-42A4-B7DB-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> newsE0EA4CB-85FA-4C78-A66A-(E-Mail Removed)...
> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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" <(E-Mail Removed)> wrote in message
> > news:06477886-AD14-42A4-B7DB-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > >> newsE0EA4CB-85FA-4C78-A66A-(E-Mail Removed)...
> > >> > 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
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
news:57CDF5DB-C13E-4CD3-BCE8-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
>> > news:06477886-AD14-42A4-B7DB-(E-Mail Removed)...
>> > > 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" <(E-Mail Removed)> wrote in message
>> > >> newsE0EA4CB-85FA-4C78-A66A-(E-Mail Removed)...
>> > >> > 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
>> > >> >>
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

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
((E-Mail Removed)).

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" <(E-Mail Removed)> wrote in message
> news:57CDF5DB-C13E-4CD3-BCE8-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> >> > news:06477886-AD14-42A4-B7DB-(E-Mail Removed)...
> >> > > 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" <(E-Mail Removed)> wrote in message
> >> > >> newsE0EA4CB-85FA-4C78-A66A-(E-Mail Removed)...
> >> > >> > 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
> >> > >> >>
> >> > >> >>
> >> > >> >>
> >> > >>
> >> > >>
> >> > >>
> >> >
> >> >
> >> >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:E78F3AF3-A3EA-4CDB-BCDE-(E-Mail Removed)...
>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
> ((E-Mail Removed)).
>
> 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" <(E-Mail Removed)> wrote in message
>> news:57CDF5DB-C13E-4CD3-BCE8-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
>> >> > news:06477886-AD14-42A4-B7DB-(E-Mail Removed)...
>> >> > > 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" <(E-Mail Removed)> wrote in message
>> >> > >> newsE0EA4CB-85FA-4C78-A66A-(E-Mail Removed)...
>> >> > >> > 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
>> >> > >> >>
>> >> > >> >>
>> >> > >> >>
>> >> > >>
>> >> > >>
>> >> > >>
>> >> >
>> >> >
>> >> >
>>
>>
>>