We have an application written in Excel 2002 VBA that deals with
user-selected cells. The user does not select the cells in any specific
order, nor does he select them from a single column as in this case. However,
the cells must be processed in exact row/column order. We have therefor
sorted the selected cells
in that order and are now about to combine them to a Range using the
following code:

Dim rngSel As Range
Dim strRange As String
Set rngSel = Worksheets("Lines").Range(strRange)

' strRange contains the following list of cells, which are selected by the
user. In this case some of them are
' in a contiguous range:
strRange =
"U4,U5,U6,U7,U8,U9,U10,U11,U12,U13,U14,U15,U16,U17 ,U18,U19,U20,U21,U22,U23,U24,U25,U26,U27,U28,U29,U 30,U31,U32,U33,U34,U35,U36,U37,U38,U39,U40,U41,

U42,U43,U44,U45,U46,U47,U48,U49,U50,U51,U52,U53,U5 4,U55,U56,U57,U58,U59,U60,U61,U62,U63,U64,U66,U66, U67,U68,U69,U70,U71,U72,U73,U74,U77,U76,U77,U78,U7 9,U80"

The "Set Range" statement fails with message: "1004 - Method 'Range' of
object '_Global' failed".

However, if "strRange" only holds up to 65 single cell-addreses, the code
executes correct. But as soon there are 66
or more, it fails with the above error message.
(In this particular case we could set the "strRange" to "U4:U80", but that
would not help, as the user could instead
select cells such as: "B2,C3,D4,E7,D8,B9,F10.....etc.").

We have not been able to find any documentation on a limitation in the
number of individual cells you can specify in order to form a Range.

I hope someone can help ?

Thanks,
Thomas

OK found what causes the problem...

The number of chars in the parameter array for the range exceeds 255 ...

"THA" wrote:

We have an application written in Excel 2002 VBA that deals with
user-selected cells. The user does not select the cells in any specific
order, nor does he select them from a single column as in this case. However,
the cells must be processed in exact row/column order. We have therefor
sorted the selected cells
in that order and are now about to combine them to a Range using the
following code:

Dim rngSel As Range
Dim strRange As String
Set rngSel = Worksheets("Lines").Range(strRange)

' strRange contains the following list of cells, which are selected by the
user. In this case some of them are
' in a contiguous range:
strRange =
"U4,U5,U6,U7,U8,U9,U10,U11,U12,U13,U14,U15,U16,U17 ,U18,U19,U20,U21,U22,U23,U24,U25,U26,U27,U28,U29,U 30,U31,U32,U33,U34,U35,U36,U37,U38,U39,U40,U41,

U42,U43,U44,U45,U46,U47,U48,U49,U50,U51,U52,U53,U5 4,U55,U56,U57,U58,U59,U60,U61,U62,U63,U64,U66,U66, U67,U68,U69,U70,U71,U72,U73,U74,U77,U76,U77,U78,U7 9,U80"

The "Set Range" statement fails with message: "1004 - Method 'Range' of
object '_Global' failed".

However, if "strRange" only holds up to 65 single cell-addreses, the code
executes correct. But as soon there are 66
or more, it fails with the above error message.
(In this particular case we could set the "strRange" to "U4:U80", but that
would not help, as the user could instead
select cells such as: "B2,C3,D4,E7,D8,B9,F10.....etc.").

We have not been able to find any documentation on a limitation in the
number of individual cells you can specify in order to form a Range.

I hope someone can help ?

Thanks,
Thomas