Non-contiguous Range limit?

  • Thread starter Thread starter Mark Dabbs
  • Start date Start date
M

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
 
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 said:
Hi all

I have a grid of non-contiguous cells that I wish to give a name from
Click to expand...
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
Click to expand...
non-contigous range an error message is thrown up
 
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 said:
Hi all

I have a grid of non-contiguous cells that I wish to give a name from
Click to expand...
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
Click to expand...
non-contigous range an error message is thrown up
 
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
 
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 said:
Thanks to all for the info - somewhat depressing after I thought that I'd
Click to expand...
sorted out a smart way to sequentially number
the cells within my non-contiguous range from 1 to n using a variety of
Click to expand...
different "routes" through the same original
 
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
Click to expand...
 
Back
Top