Excel/VBA: Chart.SetSourceData is changing the range by itself

[baldmosher]

Excel 2010

I'm trying to update a chart source data range with VBA but it's pulling in the wrong range.

where the data source is "graph_01" because it's January data (stats per day, grouped 1 month per table):

    Set rG = rH.Cells(1)  'first cell in header row
    Set rG = Range(rG, rG.End(xlToRight))   '...to last cell in header row
    Set rG = Union(rG, rG.Offset(3), rG.Offset(4))   'include the two rows of data relevant to the chart
    Set nm = ActiveSheet.Names.Add("graph_01", rG)   'add the range name "graph_01"
    rG.Select     'just for debugging

So.... nm and rG and Selection all refer to "=York!$A$7:$X$7,York!$A$10:$X$11"

however when it comes to the next line....

    ActiveSheet.ChartObjects("Chart_01").Chart.SetSourceData rG, xlRows

Excel is inexplicably loading the source data range as: =York!$A$7:$X$8,York!$A$11:$X$11

If I try to specify the source data range manually, via right-click > Select Data, it makes no difference, it still changes it back!

Any clues as to why Excel is being demented? I'm assuming the fact that I'm spanning multiple ranges has something to do with it. If so is there a common fix/workaround?

 

[baldmosher]

Well, I've not exactly figured out why it's happening, but it seems that unless I specify the various axis options (or amend them manually) Excel is trying to guess what type of chart it should be and where the axes should be. Specifying/correcting them all explicitly apparently fixes the issue.

With cht
    .SetSourceData Source:=Rows("91:93")
    .SeriesCollection(1).Delete
    .SeriesCollection(1).XValues = "=York!$B$91:$W$91"
End With

 

[baldmosher]

Ah, I spoke too soon... it's not reliable. Only fix seems to be to make it a continuous range!