Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Charts forget range or data; fixed by manual alteration then Undo
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: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. |
#2
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Charts forget range or data; fixed by manual alteration then Undo
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. "Garth T Kidd" wrote: 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: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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charts: using a filtered list as a data range | New Users to Excel | |||
Vlookup to Return a Range of Data | Excel Discussion (Misc queries) | |||
Add a data series dynamically to a named range? | Charts and Charting in Excel | |||
Setting source data range with Charts | Charts and Charting in Excel | |||
Charts not recognizing source data if original linked data is changed. | Charts and Charting in Excel |