Setting SeriesCollection values in Excel 2000

[nubee]

Depending on the location of data range, I set the values using either

1. ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R1C1:R50C1"
or
2. ActiveChart.SeriesCollection(1).XValues = _
"=(Sheet1!R1C1,Sheet1!R3C1,Sheet1!R5C1,...,Sheet1!R49C1)"

Case 1 works fine since cell range is continuous.

Case 2 works also provided the formula string length is less than 254 characters.

The formula string is built within a Function of type String. There seems to be
a limitation on the length of the result string since it gets truncated if the length
exceeds 254.

I wonder if there are work-around alternatives for 2.

TIA,
Long

 

[pikus]

That depends… is there a certain set of criteria that you’re using t
determine whether or not a certain cell is included? You may be bette
off mirroring the same data contiguously in a hidden row, column o
sheet and pointing the chart at THAT data. - Piku

 

[nubee]

: That depends. is there a certain set of criteria that you're using to
: determine whether or not a certain cell is included? You may be better
: off mirroring the same data contiguously in a hidden row, column or
: sheet and pointing the chart at THAT data. - Pikus
:
By the time I set the series collection values I do know the exact cells
that are included for each series. Mirroring would require more effort as
other data columns are also involved.

Sorting is also another option, but a specific order need to be kept, as
specified in the category column. Sorting by the series column does not
guarantee a consistent ordering of the category column.

Thanks for the suggestion,
Long

 

[pikus]

But having the data in another (hidden) worksheet would avoid th
problem of disturbing the rest of the data entirely. - Piku

 

[Jon Peltier]

And it seems worth a little more effort to do the mirroring, if it makes
the chart actually work.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______