Clearing contents of unprotected cells

[bigmacneb]

I have a workbook with protected text and formula cells, and unprotected cells which are typed into. I want to clear the contents of the unprotected cells within the worksheet. obviously highlighting the sheet and hitting delete won't do the trick because it won't let you delete the protected cells. Any thoughts??? Thanks

 

[Smitty]

If it's not a huge range, you can use something like this:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> Macro1()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> ActiveSheet.UsedRange
            <SPAN style="color:#00007F">If</SPAN> c.Locked = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN> c.ClearContents
        <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

 

[erik.van.geit]

Hi, guys,
try this

Sub name_unprotected_range_each_sheet()
'Erik Van Geit
'051031
'to clear at once the contents off all unprotected cells on a worksheet
'you need to name the range with those cells
'when to much areas, code will bug

Dim UA As Range
Dim C As Range
Dim WS As Worksheet

For Each WS In Worksheets
Set UA = Nothing
    For Each C In WS.UsedRange
        If C.Locked = False Then
        If UA Is Nothing Then Set UA = C Else Set UA = Union(UA, C)
        End If
    Next C
If Not UA Is Nothing Then _
WS.Names.Add Name:="UnprotectedArea", RefersTo:="=" & UA.Address
Next WS

End Sub

Sub clear_it()
'clears unprotected range on the activesheet
Range("UnprotectedArea").ClearContents
End Sub

kind regards,
Erik

 

[bigmacneb]

Wow, I have no idea how to do either of those. Sorry to be so basic. Could you step me through it?

 

[erik.van.geit]

start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the code

to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)

How to learn with example-codes...
1. use the macro recorder to see how code is generated (such code will need some "cleaning" afterwards)
2. "step" through it with the function key F8, while observing what's going on on your sheet
3. click on an item and hit F1 to let popup the help
4. add some steps to see what's going on in your code
example
add: MsgBox "you're currently on this sheet :" & ActiveSheet.Name & Chr(10) & Cell(i, j).Address & "will be filled with the value" & tmp
or before running click in the line and hit F9 (inserting a breakpoint)
5. ENJOY !!

Erik

 

[bigmacneb]

Great, I am starting to get it, however now I see that it will not work on merged cells. Any workaround?

 

[erik.van.geit]

reproducing an error using merged cells was impossible for me
can you explain in what situation you get into trouble ?

 

[bigmacneb]

Is it more complicated than it should be? I keep running into errors, due to the merging of cells I get Error code 1004. It also won't work for more than 8 cells. Is there a different way to approach it? Essentially there are 33 cells which need to be cleared. I can't just clear the whole page because there are protected formulas and formats. Its going to be the same cells everytime, so I'm looking to create a button to click which then clears the contents of those 33 cells and prepares it for the next input of data. possible without a macro?

 

[Smitty]

Its going to be the same cells everytime

In that case, record a macro selecting all 33 cells (you can select multiple, non-contiguous cells with CTRL+Left-Click). Once selected, hit delete. You can then assign that code to a button. Possible to do without code? No.

Smitty

 

[erik.van.geit]

this is not normal to my sense
as always I tested my code before posting
I cannot imagine any situtation to get problems, unless your worksheet is already protected ?!!

now again clicked on a lot of cells, colored them yellow to recognize, unlocked, merged some of them
no problem at all

8 cells is largely under the allowed limit
what Excel do you use ?

your comments are too general to be clear
what cells are unprotected ?
what cells are merged ?
what are the error messages ?

perhaps email your workbook to me
I'll take a look
this could get as rapidly to a solution

anyway Smittys code should work

best regards,
Erik