Charting non-sequential cells

[sqlfan13]

I am attempting to generate dynamic column charts based on grouped data in a
separate sheet. The user selects the data, and after putting the sheet
together, the chart needs to graph This Year and Last Year as separate
series, but use only Country, Region, and Unit, no sub-Unit
I am looping through the rows, and putting together the string to assign to
..Values for each series, but with a change to the structure of the data, now
I am getting a much too long string to assign to .Values (255 char. limit)

The basic string for the first series that I get from my loop is 308
characters.
strSeries1 =
"=Summary!$E$14,Summary!$E$15,Summary!$E$16,Summary!$E$23,Summary!$E$24,Summary!$E$27,Summary!$E$31,Summary!$E$35,Summary!$E$38,Summary!$E$39,Summary!$E$45,Summary!$E$46,Summary!$E$48,Summary!$E$52,Summary!$E$55,Summary!$E$56
Summary!$E$58,Summary!$E$59,Summary!$E$61,Summary!$E$62,Summary!$E$64,Summary!$E$65"

I have tried assigning the first cell to the .Values property and using
..Extend, but that has not worked for me either. I have checked out every
conceivable site that i have found, and yet nothing has worked. Please
Help!!!

The data on the Summary sheet is basically formatted as follows:
A E F
Last Year This Year
Country
Region
Unit
Sub-Unit
Sub-Unit
Unit
Sub-Unit
Region
Unit
Sub-Unit
Region
Unit
Sub-Unit
Sub-Unit

 

[Jon Peltier]

Since it's not really a dynamic solution anyway, why not copy the cells to
another range, without the gaps. Then you're back to the ideal, charting
contiguous regions.

- Jon

 

[sqlfan13]

Thanks Jon!

While it didn't seem as simple as it was, your answer made me realize that
was exactly what needed to be done.