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