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

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

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

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

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

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