Clearing contents of unprotected cells

bigmacneb

Board Regular
Joined
Jul 12, 2005
Messages
93
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
Hi, guys,
try this
Code:
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
 
Upvote 0
Wow, I have no idea how to do either of those. Sorry to be so basic. Could you step me through it?
 
Upvote 0
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
 
Upvote 0
Great, I am starting to get it, however now I see that it will not work on merged cells. Any workaround?
 
Upvote 0
reproducing an error using merged cells was impossible for me
can you explain in what situation you get into trouble ?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,852
Messages
6,104,367
Members
447,902
Latest member
chriswebs23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top