Range() using a long string variable

Question

I create a chart in Excel using this VBA.

myRange = "Sheet1!$A$1,Sheet1!$A$3,Sheet1!$A$8,Sheet1!$A$13,Sheet1!$A$18,Sheet1!$A$23,Sheet1!$A$28,Sheet1!$A$34,Sheet1!$A$41,Sheet1!$A$48,Sheet1!$D$1,Sheet1!$D$3,Sheet1!$D$8,Sheet1!$D$13,Sheet1!$D$18,Sheet1!$D$23,Sheet1!$D$28,Sheet1!$D$34,Sheet1!$D$41,Sheet1!$D$48"
    
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range( _
    myRange _
    )

When I add another couple of cells into the myRange variable (...,Sheet1!$A$55... ,Sheet1!$D$55), like this:

myRange = "Sheet1!$A$1,Sheet1!$A$3,Sheet1!$A$8,Sheet1!$A$13,Sheet1!$A$18,Sheet1!$A$23,Sheet1!$A$28,Sheet1!$A$34,Sheet1!$A$41,Sheet1!$A$48,Sheet1!$A$55,Sheet1!$D$1,Sheet1!$D$3,Sheet1!$D$8,Sheet1!$D$13,Sheet1!$D$18,Sheet1!$D$23,Sheet1!$D$28,Sheet1!$D$34,Sheet1!$D$41,Sheet1!$D$48,Sheet1!$D$55"

I get this error:

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

rotabor · Accepted Answer · 2025-05-25 18:20:24Z

4

It's the limit of 255 characters. Use

myRange = "$A$1,$A$3,..."
    
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Sheet1.Range( _
    myRange _
    )

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

answered May 25 at 18:20

rotabor

5,6112 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marco May 26 at 20:01

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 May 27 at 7:50

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".

CDP1802 · Accepted Answer · 2025-05-25 19:28:30Z

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))

edited May 25 at 19:28

answered May 25 at 19:21

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

1 Comment

rotabor May 25 at 19:39

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

Collectives™ on Stack Overflow

Range() using a long string variable

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related