| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unprotected cells | Excel Worksheet Functions | |||
| Protected/Unprotected Cells | Excel Discussion (Misc queries) | |||
| Clearing cells without clearing formulas | Excel Discussion (Misc queries) | |||
| tab between unprotected cells | Excel Discussion (Misc queries) | |||
| put zero value to unprotected cells | Excel Programming | |||
Linear Mode
