Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete contents unlocked cells
Could someone please provide me with some code to clear contents of all
unlocked cells in a worksheet? Rob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 "aktulis terlet" 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 "aktulis terlet" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete contents in cells with macro | Excel Discussion (Misc queries) | |||
Delete contents of cells | Excel Worksheet Functions | |||
Moving from unlocked cells to unlocked cells in an excel form | Excel Worksheet Functions | |||
Delete contents of unlocked cells in another worksheet | Excel Discussion (Misc queries) | |||
How do I delete the contents of unprotected cells only? | Excel Discussion (Misc queries) |