Range() using a long string variable

Question

I create a chart in Excel using this VBA.

myRange = "Sheet1!$A1,ドルSheet1!$A3,ドルSheet1!$A8,ドルSheet1!$A13,ドルSheet1!$A18,ドルSheet1!$A23,ドルSheet1!$A28,ドルSheet1!$A34,ドルSheet1!$A41,ドルSheet1!$A48,ドルSheet1!$D1,ドルSheet1!$D3,ドルSheet1!$D8,ドルSheet1!$D13,ドルSheet1!$D18,ドルSheet1!$D23,ドルSheet1!$D28,ドルSheet1!$D34,ドルSheet1!$D41,ドルSheet1!$D48ドル"
 
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range( _
 myRange _
 )

When I add another couple of cells into the myRange variable (...,Sheet1!$A55ドル... ,Sheet1!$D55ドル), like this:

myRange = "Sheet1!$A1,ドルSheet1!$A3,ドルSheet1!$A8,ドルSheet1!$A13,ドルSheet1!$A18,ドルSheet1!$A23,ドルSheet1!$A28,ドルSheet1!$A34,ドルSheet1!$A41,ドルSheet1!$A48,ドルSheet1!$A55,ドルSheet1!$D1,ドルSheet1!$D3,ドルSheet1!$D8,ドルSheet1!$D13,ドルSheet1!$D18,ドルSheet1!$D23,ドルSheet1!$D28,ドルSheet1!$D34,ドルSheet1!$D41,ドルSheet1!$D48,ドルSheet1!$D55ドル"

I get this error:

Run-time error '1004'
Method 'Range' of object '_Global' failed

Answer 1

It's the limit of 255 characters. Use

myRange = "$A1,ドル$A3,ドル..."
 
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Sheet1.Range( _
 myRange _
 )

instead, where Sheet1 is the code name of "Sheet1".

• Marco: Thanks. It is working now. How about if the sheet grows until I reach the 255 characters limit, even taking off 'Sheet1', from it?
• rotabor: In this case you need to optimize the references as per @CDP1802 answer, or use Union method, or put values (or their copies) into the continuous range like "X1:X10".

Answer 2

Dim rng as range
Set rng = Sheet1.Range("A1,A3,A8,A13,A18,A23,A28,A34,A41,A48,A55")
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Union(rng, rng.Offset(, 3))

• rotabor: Yes, the absolute address notation has no sense in this context. "Union"+"Offset" also adds more clarity.