limit of named ranges

[Pascal]

Hello
do you know ?
How many cells can man call under the same name in a worksheet?
Can we expand the limit with vba ?
How many "named range" (plage de cellules fusionnées auxquelles j'ai donné
un nom) can we put in a worksheet?
thanks


http://www.scalpa.info

 

[Don Guillett]

Homework?

 

[Dave Peterson]

#1. There's a limit of how many characters you can use in the
Insert|Name|Define dialog (I think 256, but not sure).

And you can select the cells in the worksheet (click on the first and ctrl-click
on subsequent), then use VBA to name that selection:

Selection.name = "MyRange"

#2. Lots and lots. I think the most I used was about 1000 in the workbook. If
you're having trouble, you may want to share how many you're using.

 

[Pascal]

Don Guillett said :
Homework?

not really but
almost... http://www.scalpa.info ;0}

 

[Pascal]

hello
Dave said :

And you can select the cells in the worksheet (click on the first and
ctrl-click
on subsequent), then use VBA to name that selection:

Selection.name = "MyRange"

that's what I did, but after several CTRL clic excel doesn't accept anymore
clic !!!

So I have to create four "named ranges" instead of one ! It's not cool !

 

[Dave Peterson]

How many is several.

I did a few and then did this in the immediate window:
selection.name = "Test"
?activesheet.range("Test").cells.count
101
(I clicked on 101 cells)

You could also use a 5th name that combines the four.

In code:
union(range("test1"),range("test2"), ....).name = "AllTest"

Via the insert|Name|dialog

Names in workbook:
AllTest

Refers to:
=test1,test2,test3,test4

But I'd try selecting once again.

 

[Gord Dibben]

Dave

Is OP running into the 255 character limit in the "refers to:" dialog?


Gord

 

[Dave Peterson]

I don't think so.

It looks like he was trying to use selection.name in the VBE and that didn't
work.

But maybe I misinterpreted.

 

[Gord Dibben]

Or I'm not paying attention to beginning of thread.

Better chance of that<g>


Gord

 

[Dave Peterson]

I'm afraid to ask about that other invoice thread...but the curiosity is killing
me.

Did you get the email and did you get it to work?

(shuddering in anticipation)

 

[Gord Dibben]

Still having problems.

The copy Karen sent me is not recognized by the Tempate Wizard so is useless.

I sent her a good copy which she can customize if it works for her.

The template is accessed by many on a network so some mods will need to be made.

Awaiting her next report by email.


Gord

 

[Dave Peterson]

Thanks for the update.

(And to the OP: Sorry about hijacking your thread.)