Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non-contiguous Range limit?
Hi all
I have a grid of non-contiguous cells that I wish to give a name from within a Subroutine either by using Name.Add "MyName", Selection or by using rngTemp.Name="MyName". The problem is that once I get over about 220 distinct cells in the non-contigous range an error message is thrown up each time I try to give it a name. Any fewer cells and it works fine. Is this an Excel 2000 limit? Thanks Mark --------------- www.mfdabbs.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non-contiguous Range limit?
Hi Mark:
I have come across this limit in Excel 2002 as well. I believe it is in the neighborhood of 40 cells. Regards, Vasant. "Mark Dabbs" wrote in message ... Hi all I have a grid of non-contiguous cells that I wish to give a name from within a Subroutine either by using Name.Add "MyName", Selection or by using rngTemp.Name="MyName". The problem is that once I get over about 220 distinct cells in the non-contigous range an error message is thrown up each time I try to give it a name. Any fewer cells and it works fine. Is this an Excel 2000 limit? Thanks Mark --------------- www.mfdabbs.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non-contiguous Range limit?
The limit is on the length of the string that you use in the Refersto part
of the name. Can't be longer then 1024 characters when expressed in R1C1 notation. -- Regards, Tom Ogilvy Mark Dabbs wrote in message ... Hi all I have a grid of non-contiguous cells that I wish to give a name from within a Subroutine either by using Name.Add "MyName", Selection or by using rngTemp.Name="MyName". The problem is that once I get over about 220 distinct cells in the non-contigous range an error message is thrown up each time I try to give it a name. Any fewer cells and it works fine. Is this an Excel 2000 limit? Thanks Mark --------------- www.mfdabbs.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non-contiguous Range limit?
Thanks, Tom; I never would have guessed the reason!
Regards, Vasant. "Tom Ogilvy" wrote in message ... The limit is on the length of the string that you use in the Refersto part of the name. Can't be longer then 1024 characters when expressed in R1C1 notation. -- Regards, Tom Ogilvy Mark Dabbs wrote in message ... Hi all I have a grid of non-contiguous cells that I wish to give a name from within a Subroutine either by using Name.Add "MyName", Selection or by using rngTemp.Name="MyName". The problem is that once I get over about 220 distinct cells in the non-contigous range an error message is thrown up each time I try to give it a name. Any fewer cells and it works fine. Is this an Excel 2000 limit? Thanks Mark --------------- www.mfdabbs.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non-contiguous Range limit?
Thanks to all for the info - somewhat depressing after I thought that I'd sorted out a smart way to sequentially number
the cells within my non-contiguous range from 1 to n using a variety of different "routes" through the same original pattern of cells when required! Any ideas how to proceed? Many thanks Mark On Fri, 07 Nov 2003 00:29:40 +0000, Mark Dabbs wrote: Hi all I have a grid of non-contiguous cells that I wish to give a name from within a Subroutine either by using Name.Add "MyName", Selection or by using rngTemp.Name="MyName". The problem is that once I get over about 220 distinct cells in the non-contigous range an error message is thrown up each time I try to give it a name. Any fewer cells and it works fine. Is this an Excel 2000 limit? Thanks Mark --------------- www.mfdabbs.com --------------- www.mfdabbs.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non-contiguous Range limit?
Sub Tester3()
Dim varr As Variant Dim rng As Range, i As Long Set rng = Range("B1,A1,C9,A2,M3:M5,A4:C4") ReDim varr(1 To rng.Count) i = 0 For Each cell In rng i = i + 1 varr(i) = cell.Address Next Range(varr(4)).Select End Sub -- Regards, Tom Ogilvy "Mark Dabbs" wrote in message ... Thanks to all for the info - somewhat depressing after I thought that I'd sorted out a smart way to sequentially number the cells within my non-contiguous range from 1 to n using a variety of different "routes" through the same original pattern of cells when required! Any ideas how to proceed? Many thanks Mark On Fri, 07 Nov 2003 00:29:40 +0000, Mark Dabbs wrote: Hi all I have a grid of non-contiguous cells that I wish to give a name from within a Subroutine either by using Name.Add "MyName", Selection or by using rngTemp.Name="MyName". The problem is that once I get over about 220 distinct cells in the non-contigous range an error message is thrown up each time I try to give it a name. Any fewer cells and it works fine. Is this an Excel 2000 limit? Thanks Mark --------------- www.mfdabbs.com --------------- www.mfdabbs.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Join non-contiguous ranges into one range via named range? | Excel Discussion (Misc queries) | |||
Non-Contiguous Named Range? | Excel Discussion (Misc queries) | |||
Non-contiguous named range | Excel Discussion (Misc queries) | |||
SUMIF non-contiguous range | Excel Worksheet Functions | |||
Non Contiguous range and loops | Excel Programming |