Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Formula
Is there a formula that will automatically clear ALL unprotected cells? i
have a spreadsheet and in lieu of erasing all the unprotected cells I am hoping there is a formula that with just one click it will erase all of the contents of the unproteced cells. thanks for your help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Formula
Try this small macro:
Sub ClearUm() Set r = ActiveSheet.UsedRange For Each rr In r If rr.Locked = True Then Else rr.Clear End If Next End Sub -- Gary''s Student - gsnu200907 "AD" wrote: Is there a formula that will automatically clear ALL unprotected cells? i have a spreadsheet and in lieu of erasing all the unprotected cells I am hoping there is a formula that with just one click it will erase all of the contents of the unproteced cells. thanks for your help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Formula
Formulas can't touch other cells. About the only thing that they can do is
return values to the cells that contain that formula. But you can use a macro. Do you really mean all the unlocked cells on a protected worksheet? Option Explicit Sub testme01() Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Set wks = ActiveSheet With wks Set myRng = Nothing On Error Resume Next Set myRng = .Cells.SpecialCells(xlCellTypeConstants) On Error GoTo 0 End With If myRng Is Nothing Then 'no constants to clear Else For Each myCell In myRng.Cells If myCell.Locked = False Then myCell.ClearContents End If Next myCell End If End Sub I limited my range to check to just the constants--no formulas. I figured that if you have a formula on that worksheet, then it should be saved--even if the cell isn't locked. AD wrote: Is there a formula that will automatically clear ALL unprotected cells? i have a spreadsheet and in lieu of erasing all the unprotected cells I am hoping there is a formula that with just one click it will erase all of the contents of the unproteced cells. thanks for your help -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I enter a formula, Excel shows the formula not the results | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
How do I view formula results intead of formula in excel? | Excel Worksheet Functions |