Naming non-contigous range in Excel 3

[glalsop]

Is there a limit to the number of non-contiguous cells that can be included in a named range in Excel 2000?

Twice I have highlighted the cells and named the range and both times when I check the range it only picks up about 1/3 of the original cells.

Thanks

-Gary

 

[Bowers74]

Have a look at thread68-669531!



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[xlbo]

Looks like 18 is the limit - can't find anything in help files tho - just kept selecting cells till it wouldn't add any more cells

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

The Rule of 256 will kill us all someday!!!

If you try to name a range the traditional way (Insert->Name->Define...) once the reference string gets longet then 256 characters, it craps out.

If you select the cells and then name the range by entering the name into the Name Box (upper left had corner [drop-down]) then it doesn't matter how many areas you have selected, they will all be included in the Named Range (you should never try to edit the range in the Insert->Name->Define... window though.)

There may be other limitations though (see above mentioned thread).



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[glalsop]

Hi Mike -

I was using the Name box in the upper-left hand corner when I ran in to the initial limitations, which seems to contradict your thinking here.

However, I would like to thank you for the link to the above thread, as I have now just named my Ranges 'Form_Input1', 'Form_Input2', etc. I only have about 9 such Ranges and this should allow for addidtions and deletions easily enough.

Since these are all data input ranges, perhaps you know off of the top of your head what the correct syntax is for determining the name of all named ranges on a worksheet, i.e.:

For Each NamedRange In Worksheets("Form&quot
if InStr(NamedRange, "Form_Input&quot Then
InputRangCount = InputRangCount + 1
end if
next NamedRange

Essentially, I have several sections of data to input that mimic a paper form to be sent out to clients. If I have to add a section, I just want to name it Form_Input4, or whatever, so in code I would like to determine how many ranges I have that are named like that.

Thanks


-Gary

 

[glalsop]

Nevermind, got it:

Private Sub cmdClear_Click()

Dim nm As Name
Dim iInputRanges As Integer

For Each nm In ThisWorkbook.Names
If InStr(nm.Name, &quot;Form_Input&quot <> 0 Then
iInputRanges = iInputRanges + 1
End If
'Debug.Print nm.Name
Next nm

MsgBox iInputRanges

End Sub

Thanks guys!!


-Gary

 

[xlbo]

aaaah - Nice one Mike - that's what I get from trying it out just on a sheet called &quot;Sheet1&quot; ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[TonyJollans]

Interesting. I missed the other thread when it was first posted so thanks for the link, Mike.

I have done some playing and the only constraint I can find is the 256-character restriction on the various &quot;RefersTo&quot; strings. Once the range has been named, it appears that its details are held in some internal format and that the strings are (re)built when needed. To see some interesting effects of this (and I'll leave interested people to experiment on their own):

Rename a Sheet to &quot;a&quot; - this will allow you to name a range with about 30 distinct single cell areas.
Rename the sheet to something longer.
The range still exists (you can, for example, GoTo it).
Look at the RefersToRange.Address (or RefersToR1C1) in VBA.

Next, the limit still exists when using the Name box - the difference is in the way the string is built up behind the scenes - it uses relative addresses (e.g. A1 instead of Sheet1!$A$1) so there is scope for more areas, but still a limit. Also, you don't get an error, it just doesn't take all the areas.

Lastly, I have managed to name a range of some 900 separate areas (which works) by building it up bit by bit like this:

myran1: &quot;=Sheet1!$A$1,Sheet1!$A$3&quot;
myran2: &quot;=Sheet1!$C$1,Sheet1!$C$3&quot;
myrana: &quot;=myran1,myran2&quot;

myran3: &quot;=Sheet1!$B$2,Sheet1!$B$4&quot;
myran4: &quot;=Sheet1!$D$2,Sheet1!$D$4&quot;
myranb: &quot;=myran3,myran4&quot;

bigran: &quot;=myrana,myranb&quot;


Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[xlbo]

Star to Mike for giving the true answer and to Tony for finding new and ingenius ways of getting that little bit more from XL

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244