Charts forget range or data; fixed by manual alteration then Undo

[Garth T Kidd]

G'day, everyone.

Two of my charts recently stopped displaying properly. When I refresh
the data behind them, the automatic X axis drops most of its range and
the chart either stops drawing most the ranges or collapses them to
<10% of their original size.

The most reliable way to prod the chart into displaying properly is to
make a manual alteration to the chart's configuration, and then hit
Undo. The changes themselves rarely help. Copying and pasting back to
the same range within the data can also help.

Weirdly, these tricks don't work when I run them as a macro. Here's
one I recorded. Whilst recording, it worked fine. On the replay, it
didn't.

Sub FixChartHack()
' Grotty hack to force the chart to refresh properly.
Sheets("Raw Perf Data").Select
Application.CutCopyMode = False
Selection.Copy
Columns("D").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

The charts are stacked charts of ten ranges, with data coming from one
sheet. I refresh the data from a VBA macro that deletes C:IY, then
insert-pastes C before itself enough times to stretch the range out to
its new size. That way, the chart stretches its own ranges without me
having to do it in code.

Set wSht = Sheets(SheetName)
With wSht
.Visible = True
.Activate
Columns("D:IV").Select
Selection.Delete Shift:=xlToLeft
Dim ColN As Integer
For ColN = 1 To AllDates.Count - 2
Columns("C:C").Select
Selection.Copy
Selection.Insert Shift:=xlToRight
Next
Cells(1, 1).Select
End With
' ... and then I shove numbers into the cells.

I'm running into this on Excel 2003, fully patched. Everything was
working fine for a few days I was working on the sheet, but now it's
abruptly stopped working. I can't ship the sheet until I can trust
that it'll reliably work for its users.

Could you give me a solid lead on how to work around this bug? How can
I force the chart to update itself properly?

Yours,
Garth.

 

[RominallL]

Don't know if this will really help but I've found that using named ranges in
chart series saves a lot of headaches when you update the data.

Here's a sample: Note that the ranges might need to be re-named when
adjusted but I find it's easier to re-set a named range than trying to fix a
chart.

ActiveChart.SetSourceData Source:=Sheets("2-Cons").Range("ResNames," +
MonthN + ""), _
PlotBy:=xlColumns

HTH.