Non-contiguous Range limit?

[Mark Dabbs]

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

 

[Vasant Nanavati]

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.

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

 

[Tom Ogilvy]

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

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

 

[Vasant Nanavati]

Thanks, Tom; I never would have guessed the reason!

Regards,

Vasant.

 

[Mark Dabbs]

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

 

[Tom Ogilvy]

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

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

 

[Mark Dabbs]

Just what I need! - Many thanks again Tom

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