Afternoon Bob,

I did as you mentioned. Took:
=IncidentForm!$D$8:$D$10,IncidentForm!$G$8:$G$10,I ncidentForm!$J$8
=IncidentForm!$D$19,IncidentForm!$G$19,IncidentFor m!$D$21,IncidentForm!$F$21,IncidentForm!$I$21,Inci dentForm!$M$21
=IncidentForm!$L$26,IncidentForm!$L$29,IncidentFor m!$L$31,IncidentForm!$L$33
=IncidentForm!$D$5:$D$6,IncidentForm!$G$5:$G$6,Inc identForm!$J$4:$J$6
=IncidentForm!$D$27,IncidentForm!$F$27,IncidentFor m!$I$27,IncidentForm!$K$27
=IncidentForm!$D$12:$D$14,IncidentForm!$G$12:$G$14 ,IncidentForm!$J$12:$J$14,IncidentForm!$M$12:$M$14 ,IncidentForm!$D$16,IncidentForm!$G$16,IncidentFor m!$I$16,IncidentForm!$K$16,IncidentForm!$D$17,Inci dentForm!$G$17,IncidentForm!$I$17

And removed IncidentForm! to reduce it to this:
=$D$8:$D$10,$G$8:$G$10,$J$8,$D$19,$G$19,$D$21,$F$2 1,$I$21,$M$21,$L$26,$L$29,$L$31,$L$33,$D$5:$D$6,$G $5:$G$6,$J$4:$J$6,$D$27,$F$27,$I$27,$K$27,$D$12:$D $14,$G$12:$G$14,$J$12:$J$14,$M$12:$M$14,$D$16,$G$1 6,$I$16,$K$16,$D$17,$G$17,$I$17

And when I copied from Word back to Excel into the Refers tobox
It put the IncidentForm! infront of each cell thus limiting the Refers to
$L$29 (see below):
=IncidentForm!$D$8:$D$10,IncidentForm!$G$8:$G$10,I ncidentForm!$J$8,IncidentForm!$D$19,IncidentForm!$ G$19,IncidentForm!$D$21,IncidentForm!$F$21,Inciden tForm!$I$21,IncidentForm!$M$21,IncidentForm!$L$26, IncidentForm!$L$29 IncidentForm!

Did I follow your instructions correctly?


"Bob Phillips" wrote:

I would guess it is because the Refersto value is too long. When you select
a range, say A1:A10 and try to assign a name to it, Excel automatically adds
the sheet name, Sheet1!A1:A10.

So if you select three ranges, B4:B7, e$, H4 you will get

=Sheet1!$B$4:$B$7,Sheet1!$E$9,Sheet1!$H$4

which means that it runs out of room (255 chars) very quickly in your case.

What you could do is to go into the name definition, Ctrl-F3, select your
name and then go edit the RefersTo box, select it and F2. Remove all the
Sheet1! bits from what is already there, and then you can manuallt type some
more on the end (without the sheet name. Excel will add the sheet name
again, but you have got past the 255 restriction.

What I tend to do is to copy the value in RefersT, paste it into a decent
text editor (not Notepad), and edit it there with toools such as find &
Replace. It is easier to see what is going on. Then just paste it back.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Sunnyskies" wrote in message
...
Thanks Gary, but is it not possible instead of listing each cell, rather
use
a range name?

"Gary''s Student" wrote:

Names may have limitations that string do not have. Try something like:


Sub sistence()
Dim CLEAR_OLD_DATA As String, r As Range
CLEAR_OLD_DATA = "A1:C9,A11:D13"
Range(CLEAR_OLD_DATA).Clear
End Sub

--
Gary's Student


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but
the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or
row.

The purpose of the name is to group those cells that need to cleared
when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks