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

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

--
Message posted from http://www.ExcelForum.com

"pikus " wrote in message
...
: 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

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

--
Message posted from http://www.ExcelForum.com

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/
_______

pikus < wrote:

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


---
Message posted from http://www.ExcelForum.com/