Clearing Unprotected Cells
 

What would the macro code be to clear all unprotected
cells in the current worksheet? Would put this macro in
Personal.xls

Clearing Unprotected Cells
 

for each cell in Activesheet.UsedRange
if not cell.Locked then
cell.clearcontents
end if
Next


or assuming you wouldn't clear formulas


for each cell in Activesheet.UsedRange.SpecialCells(xlconstants)
if not cell.Locked then
cell.clearcontents
end if
Next
' or if you would add
for each cell in Activesheet.UsedRange.SpecialCells(xlformulas)
if not cell.Locked then
cell.clearcontents
end if
Next
--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
What would the macro code be to clear all unprotected
cells in the current worksheet? Would put this macro in
Personal.xls

Clearing Unprotected Cells
 

Thanks again, Tom
-----Original Message-----
for each cell in Activesheet.UsedRange
if not cell.Locked then
cell.clearcontents
end if
Next


or assuming you wouldn't clear formulas


for each cell in Activesheet.UsedRange.SpecialCells
(xlconstants)
if not cell.Locked then
cell.clearcontents
end if
Next
' or if you would add
for each cell in Activesheet.UsedRange.SpecialCells
(xlformulas)
if not cell.Locked then
cell.clearcontents
end if
Next
--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
What would the macro code be to clear all unprotected
cells in the current worksheet? Would put this macro in
Personal.xls


.

Error on merged cells
 

Tom, a wrinkle - getting: Run-time error '1004': Cannot
change part of a merged cell. "If not..." line yellow.
What next?

Thanks, Phil
-----Original Message-----
for each cell in Activesheet.UsedRange
if not cell.Locked then
cell.clearcontents
end if
Next


or assuming you wouldn't clear formulas


for each cell in Activesheet.UsedRange.SpecialCells
(xlconstants)
if not cell.Locked then
cell.clearcontents
end if
Next
' or if you would add
for each cell in Activesheet.UsedRange.SpecialCells
(xlformulas)
if not cell.Locked then
cell.clearcontents
end if
Next
--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
What would the macro code be to clear all unprotected
cells in the current worksheet? Would put this macro in
Personal.xls


.

Error on merged cells
 

You could do it for just the first cell in the mergearea or even simpler:

cell.value = ""
instead of
cell.clearcontents



Phil Hageman wrote:

Tom, a wrinkle - getting: Run-time error '1004': Cannot
change part of a merged cell. "If not..." line yellow.
What next?

Thanks, Phil
-----Original Message-----
for each cell in Activesheet.UsedRange
if not cell.Locked then
cell.clearcontents
end if
Next


or assuming you wouldn't clear formulas


for each cell in Activesheet.UsedRange.SpecialCells
(xlconstants)
if not cell.Locked then
cell.clearcontents
end if
Next
' or if you would add
for each cell in Activesheet.UsedRange.SpecialCells
(xlformulas)
if not cell.Locked then
cell.clearcontents
end if
Next
--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
What would the macro code be to clear all unprotected
cells in the current worksheet? Would put this macro in
Personal.xls


.


--

Dave Peterson

Error on merged cells
 

for each cell in Activesheet.UsedRange
if not cell.Locked then
cell.MergeArea.clearcontents
end if
Next

--
Regards,
Tom Ogilvy


Phil Hageman wrote in message
...
Tom, a wrinkle - getting: Run-time error '1004': Cannot
change part of a merged cell. "If not..." line yellow.
What next?

Thanks, Phil
-----Original Message-----
for each cell in Activesheet.UsedRange
if not cell.Locked then
cell.clearcontents
end if
Next


or assuming you wouldn't clear formulas


for each cell in Activesheet.UsedRange.SpecialCells
(xlconstants)
if not cell.Locked then
cell.clearcontents
end if
Next
' or if you would add
for each cell in Activesheet.UsedRange.SpecialCells
(xlformulas)
if not cell.Locked then
cell.clearcontents
end if
Next
--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
What would the macro code be to clear all unprotected
cells in the current worksheet? Would put this macro in
Personal.xls


.

Error on merged cells
 

Thanks, Tom - works great. To speed things up, I took a
line from something you gave me earlier:
Application.ScreenUpdating = False/True

Thanks again.

-----Original Message-----
for each cell in Activesheet.UsedRange
if not cell.Locked then
cell.MergeArea.clearcontents
end if
Next

--
Regards,
Tom Ogilvy


Phil Hageman wrote in message
...
Tom, a wrinkle - getting: Run-time error '1004': Cannot
change part of a merged cell. "If not..." line yellow.
What next?

Thanks, Phil
-----Original Message-----
for each cell in Activesheet.UsedRange
if not cell.Locked then
cell.clearcontents
end if
Next


or assuming you wouldn't clear formulas


for each cell in Activesheet.UsedRange.SpecialCells
(xlconstants)
if not cell.Locked then
cell.clearcontents
end if
Next
' or if you would add
for each cell in Activesheet.UsedRange.SpecialCells
(xlformulas)
if not cell.Locked then
cell.clearcontents
end if
Next
--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
What would the macro code be to clear all unprotected
cells in the current worksheet? Would put this
macro in
Personal.xls


.



.