Insert-Name-Define limit ?

S

Sunnyskies

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
 
G

Gary''s Student

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
 
P

PapaDos

The "Refers to" property has a limit of 255 characters.

You can get around this limitation by defining the named range this way:
1 - Select all the cells you want to include in the named range
2 - Enter the name of the range in the "Name box" of the formula bar
(search Excel's help for "name range").
This technique works fine in Excel, but may cause surprises if you
manipulate the "Names" collection through VBA (rarely an issue).

Another way, is to create names for part of your range and use those names
to create the larger named range...
 
B

Bob Phillips

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)
 
R

Roger Govier

Hi

Your problem is the length of the names. They average just over 20
characters in length each, of which the first 14 are repeated.
If you try to add them all together into one range name, you would only
manage 11 or 12 before running into the 255 character limit. I believe
you said you had 31 ranges.

If so, do it in 2 stages.
rng1 = 11 of your ranges, rng2 the next 11 and rng3 the final 9
Then Clear_Old_Data =rng1,rng2,rng3
 
B

Bob Phillips

See my response earlier

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Top