Clearing contents of unprotected cells in protected sheet.

[Gryndar]

I would like to be able to clear the contents of ALL unprotected cells at
once in a protected excel worksheet.

 

[JE McGimpsey]

I would like to be able to clear the contents of ALL unprotected cells at
once in a protected excel worksheet.

One way:

1) Unprotect the sheet
2) Select the unprotected cells
3) Name the cells (enter the name in the Name box
on the left side of the Formula Bar)
4) Reprotect the sheet

Now you can select the range from the name box. Press Ctrl-b to clear
the cells.

 

[RmB]

How can I edit/delete the name and does each sheet need its own distinct name for this action?
RmB

 

[CyberTaz]

To Edit a Named Range use Insert> Name> Define.

A Named Range is limited to cells on a single sheet, so Yes - if you want to
have the same ability on more than one sheet the unprotected cells on each
sheet would have to be named as separate ranges.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[RmB]

Thanks, this really helps. It's amazing how much I can learn by listening to people who actually know what they are doing.
RmB

 

[RmB]

After unprotecting a sheet, selecting approx 150 unprotected cells, naming them in the name box, and protecting the sheet I find some of the selected cells are not recognized when I select the Named Range in the name box. I go through the whole process again, selecting cells the same way but in a different order and a different number of cells are not recognized. What am I am missing? Thanks again, RmB

 

[Bob Greenblatt]

After unprotecting a sheet, selecting approx 150 unprotected cells, naming
them in the name box, and protecting the sheet I find some of the selected
cells are not recognized when I select the Named Range in the name box. I go
through the whole process again, selecting cells the same way but in a
different order and a different number of cells are not recognized. What am I
am missing? Thanks again, RmB

Can you please describe exactly what ranges you are selecting, and what the
reference is when you look at Insert-name-Define and click on the name.

 

[RmB]

The cells I am selecting are:
C3
C8,11,14,18,21,24
D8,11,14,18,21,24
AA8,11,14,18,21,24
F:N6,8,9,11,12,14,16,18,19,21,22,24
P:X6,8,9,11,12,14,16,18,19,21,22,24
AB:AF6
In the latest attempt, when I click on the name all are selected except C8 C11 C14 F:N14 P:X14
The try before this one the following cells were not selected:
C3 F:N6 P:X6 AB:AF6 AA8,11,14,18,24

 

[JE McGimpsey]

The cells I am selecting are:
C3
C8,11,14,18,21,24
D8,11,14,18,21,24
AA8,11,14,18,21,24
F:N6,8,9,11,12,14,16,18,19,21,22,24
P:X6,8,9,11,12,14,16,18,19,21,22,24
AB:AF6
In the latest attempt, when I click on the name all are selected except C8
C11 C14 F:N14 P:X14
The try before this one the following cells were not selected:
C3 F:N6 P:X6 AB:AF6 AA8,11,14,18,24

I think you're running into a limit on the length of the string in the
name's reference string. Based on informal trials, I can get a length of
up to about 235 characters to work, but if the reference produces more
than that many characters, anything more drops off.

Since the definitions use Sheet names and absolute addressing for each
range, e.g.,

=Sheet1$C$3,Sheet1$C$8:$D$8,Sheet1!$C$11:$D$11,...

you can get a few more sub-ranges in by shortening the sheet name, but
probably not enough to include all of your subranges listed above.

One workaround would be to split the ranges into smaller named ranges,
then use the union operator (,) to join them in a third named range. For
instance, if you split the above into MyRange1 and MyRange2, then you
could use Insert/Name/Define to combine them:

Names in workbook: MyRange3
Refers to: =MyRange1,MyRange2

However, as a calculated range, MyRange3 cannot then be selected from
the Name box on the Formula bar.

 

[Bob Greenblatt]

However, as a calculated range, MyRange3 cannot then be selected from
the Name box on the Formula bar.

No, but he should be able to press F5 and type myrange3 to select them all.

 

[RmB]

Gentlemen, your solution works perfectly though I'm not sure which is quicker, the F5 and typing or just selecting the two ranges in the name box. In either case thank you. One other little stickler for me, is how to have a cell that is the sum ,of say 10 cells, remain blank if any of the 10 cells is blank? Thanks again, RmB

 

[JE McGimpsey]

Gentlemen, your solution works perfectly though I'm not sure which is
quicker, the F5 and typing or just selecting the two ranges in the name box.
In either case thank you. One other little stickler for me, is how to have a
cell that is the sum ,of say 10 cells, remain blank if any of the 10 cells is
blank? Thanks again, RmB

One way:

=IF(COUNT(rng)=10,SUM(rng),"")

 

[RmB]

Again, a perfect solution. Thanks much, RmB