Prev: Trendline partial data
Next: Pivot Table-Change Data Source? Excel 2007
From: Joe M. on 19 May 2010 08:19
Is it possible to have the highest data point on a line graph plot at the
very top of a chart? For example, if the highest data point is 90 then I
would like highest value of the y axis to also be 90 placing the data point
at the top of the chart instead of excel automatically adding a "cushion
(like 10) to the y axis. The data points vary each time the chart is created
so i can't fix the value at 90.

Thanks,
Joe M.
From: ck13 on 19 May 2010 22:11
Hi Joe,

I was looking at this issue yesterday and came upon this macro. It set the
high of the axis to the highest point of the data and also the low of the
axis to the lowest point of the data. In case you want to start from 0, you
will need to modified the code a bit. Hope this is what you want.

Sub AutoScaleYAxes()
Dim ValuesArray(), SeriesValues As Variant
Dim Ctr As Integer, TotCtr As Integer
With ActiveChart
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



"Joe M." wrote:

> Is it possible to have the highest data point on a line graph plot at the
> very top of a chart? For example, if the highest data point is 90 then I
> would like highest value of the y axis to also be 90 placing the data point
> at the top of the chart instead of excel automatically adding a "cushion
> (like 10) to the y axis. The data points vary each time the chart is created
> so i can't fix the value at 90.
>
> Thanks,
> Joe M.
 | 
Pages: 1
Prev: Trendline partial data
Next: Pivot Table-Change Data Source? Excel 2007