Auto Scale Chart Macro -- Any improvements please?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is the best way I've found to correctly scale a chart -- the only way I
found to exclude a chart interpolating with #NA cells was to delete those
cells containing #NA, and run the chart. This takes a long time on my
computer. Wondering if there is a better way. This macro is directly taken
from 2 excel mvps and modified; i believe a mehta and ron bovarty, but could
be wrong. Thanks for taking a look.

Sub AutoScaleYAxes()
Dim ValuesArray(), SeriesValues As Variant
Dim Ctr As Integer, TotCtr As Integer

Application.Run "Extend_Stock_Data"
Application.Run "Delete_Row_NA_Data"

Sheets("SnapShot").Select
ActiveSheet.ChartObjects("CIQChart1s0t0").Activate
ActiveChart.PlotArea.Select
ActiveWindow.Visible = False

With ActiveChart
On Error Resume Next
For Each X In .SeriesCollection
SeriesValues = X.Values
ReDim Preserve ValuesArray(1 To TotCtr + UBound(SeriesValues))
For Ctr = 1 To UBound(SeriesValues)
ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
Next
TotCtr = TotCtr + UBound(SeriesValues)
Next
.Axes(xlValue).MinimumScaleIsAuto = True
.Axes(xlValue).MaximumScaleIsAuto = True
.Axes(xlValue).MinimumScale = Application.Min(ValuesArray)
.Axes(xlValue).MaximumScale = Application.Max(ValuesArray)
End With
End Sub


Sub Delete_Row_NA_Data()
Dim DeleteValue As String
Dim rng As Range

Sheets("data").Select
DeleteValue = "#N/A"
With ActiveSheet
.Range("D100:D1000").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End With
.AutoFilterMode = False
End With
End Sub

Sub Extend_Stock_Data()


Dim LastRow As Long


With Worksheets("data")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
..Range("b17:g17").AutoFill Destination:=.Range("b17:g" & LastRow) _
, Type:=xlFillDefault

End With

End Sub
 
I'm betting that the code is about as good as it gets. I just finished a
battle with 2007 charts also, and kind of feel like I lost <g>. You said "it
takes a long time on my computer" and that's exactly the experience I had:
charting on two separate systems takes up to 12 times as long in Excel 2007
to complete as it does for same data and same chart type in Excel 2003 on a
machine much slower than either of the two running 2007.

To give specifics: generating 51 charts, 49 with 8800 data points, 2 with
1320 data points. X-Y Scatter chart.
AMD 3200+ 1GB single core w/Excel 2003: 1m 21s to complete
AMD X2 4800+ 2GB dual-core w/Excel 2007: 12m 05s to complete!
Intel E6600 2GB 'CoreDuo' w/Excel 2007: 11m 47s to complete!

After finishing the charting, system response in Excel is snail-slow.

Also, are you seeing anything during the changing of the charts with
ActiveWindow.Visible=False ??
I was using Application.ScreenUpdating=False and the charting process forces
its way through that in Excel 2007, where it did not in 2003.
 
This can be cleaned up:

Sheets("SnapShot").Select
ActiveSheet.ChartObjects("CIQChart1s0t0").Activate
ActiveChart.PlotArea.Select
ActiveWindow.Visible = False

With ActiveChart

use this instead:

With Sheets("SnapShot").ChartObjects("CIQChart1s0t0")

I don't know how much it will help with 2007. I have yet to do any
large-scale charting in 2007, just small bits to find workarounds for one of
my clients.

- Jon
 
Back
Top