When I have the verticle axis set to automatic on the Min & Max range it
usually shows a huge distance on the chart because it generally defaults to
0. If the data is 55-100, the chart will start at 0 at the bottom and the
55-100 will be toward the top scrunching the data. Seems like it should size
up the data to best fit everything when on the auto setting. I don't want to
use a fixed setting because if the data changes then I run into a similar
problem. Is there a way to either change the default settings so the charts
will automatically size themselves, or something else I can do to make this
work for me?
--
Thank you!

Were do I need to put the macro? I tried placing it in the workbook and in
the sheet view code, but doesn't do anything. What am I doing wrong?
--
Thank you!


"ck13" wrote:

Hi,

I faced this problem and found a solution but you need to use macro.

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



Another way you can do is try this method by Jon Peltier
http://peltiertech.com/Excel/Charts/...nkToSheet.html

I have not tried his method but it should work fine as I have very good
experience using his other solutions to my charting problems.

"Doug" wrote:

When I have the verticle axis set to automatic on the Min & Max range it
usually shows a huge distance on the chart because it generally defaults to
0. If the data is 55-100, the chart will start at 0 at the bottom and the
55-100 will be toward the top scrunching the data. Seems like it should size
up the data to best fit everything when on the auto setting. I don't want to
use a fixed setting because if the data changes then I run into a similar
problem. Is there a way to either change the default settings so the charts
will automatically size themselves, or something else I can do to make this
work for me?
--
Thank you!

I have a volume-Open-High-Low-Close chart that I am using this on and it made
the volume extend to the top of the chart, but the price pattern didn't move?
Any more helpful suggestions?
--



"ck13" wrote:

Hi, tried psoting my reply but seems to have some problems. Press "Alt"+"F11"
to open the visual basic. Then at the left you will see VBAProject (Name of
your workbook"). Right click at that and insert a new module. Copy the code
into the new module and close VBA. Click on the chart that you want to change
and press "Alt"+"F8" to open the macros selection. Click on the appropriate
macros and run it.


"Doug" wrote:

Were do I need to put the macro? I tried placing it in the workbook and in
the sheet view code, but doesn't do anything. What am I doing wrong?
--
Thank you!


"ck13" wrote:

Hi,

I faced this problem and found a solution but you need to use macro.

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



Another way you can do is try this method by Jon Peltier
http://peltiertech.com/Excel/Charts/...nkToSheet.html

I have not tried his method but it should work fine as I have very good
experience using his other solutions to my charting problems.

"Doug" wrote:

When I have the verticle axis set to automatic on the Min & Max range it
usually shows a huge distance on the chart because it generally defaults to
0. If the data is 55-100, the chart will start at 0 at the bottom and the
55-100 will be toward the top scrunching the data. Seems like it should size
up the data to best fit everything when on the auto setting. I don't want to
use a fixed setting because if the data changes then I run into a similar
problem. Is there a way to either change the default settings so the charts
will automatically size themselves, or something else I can do to make this
work for me?
--
Thank you!

I tried this and works fine accept it changes the primary values. Is it
possible to have this change the secondary values instead?



"ck13" wrote:

Hi, tried psoting my reply but seems to have some problems. Press "Alt"+"F11"
to open the visual basic. Then at the left you will see VBAProject (Name of
your workbook"). Right click at that and insert a new module. Copy the code
into the new module and close VBA. Click on the chart that you want to change
and press "Alt"+"F8" to open the macros selection. Click on the appropriate
macros and run it.


"Doug" wrote:

Were do I need to put the macro? I tried placing it in the workbook and in
the sheet view code, but doesn't do anything. What am I doing wrong?
--
Thank you!


"ck13" wrote:

Hi,

I faced this problem and found a solution but you need to use macro.

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



Another way you can do is try this method by Jon Peltier
http://peltiertech.com/Excel/Charts/...nkToSheet.html

I have not tried his method but it should work fine as I have very good
experience using his other solutions to my charting problems.

"Doug" wrote:

When I have the verticle axis set to automatic on the Min & Max range it
usually shows a huge distance on the chart because it generally defaults to
0. If the data is 55-100, the chart will start at 0 at the bottom and the
55-100 will be toward the top scrunching the data. Seems like it should size
up the data to best fit everything when on the auto setting. I don't want to
use a fixed setting because if the data changes then I run into a similar
problem. Is there a way to either change the default settings so the charts
will automatically size themselves, or something else I can do to make this
work for me?
--
Thank you!

You will need to amend the VBA. I am not strong in this so you might need to
post your questions. This discussion group is going to close on 1st June. So
i strongly advise you not to post your question here. post it in this forum
instead. http://answers.microsoft.com/en-us/o...ult.aspx#tab=1

This is where I managed to get help from others to amend the VBA i gave you
to include secondary axis.



"Doug" wrote:

I tried this and works fine accept it changes the primary values. Is it
possible to have this change the secondary values instead?



"ck13" wrote:

Hi, tried psoting my reply but seems to have some problems. Press "Alt"+"F11"
to open the visual basic. Then at the left you will see VBAProject (Name of
your workbook"). Right click at that and insert a new module. Copy the code
into the new module and close VBA. Click on the chart that you want to change
and press "Alt"+"F8" to open the macros selection. Click on the appropriate
macros and run it.


"Doug" wrote:

Were do I need to put the macro? I tried placing it in the workbook and in
the sheet view code, but doesn't do anything. What am I doing wrong?
--
Thank you!


"ck13" wrote:

Hi,

I faced this problem and found a solution but you need to use macro.

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



Another way you can do is try this method by Jon Peltier
http://peltiertech.com/Excel/Charts/...nkToSheet.html

I have not tried his method but it should work fine as I have very good
experience using his other solutions to my charting problems.

"Doug" wrote:

When I have the verticle axis set to automatic on the Min & Max range it
usually shows a huge distance on the chart because it generally defaults to
0. If the data is 55-100, the chart will start at 0 at the bottom and the
55-100 will be toward the top scrunching the data. Seems like it should size
up the data to best fit everything when on the auto setting. I don't want to
use a fixed setting because if the data changes then I run into a similar
problem. Is there a way to either change the default settings so the charts
will automatically size themselves, or something else I can do to make this
work for me?
--
Thank you!

Thanks for your help


"ck13" wrote:

You will need to amend the VBA. I am not strong in this so you might need to
post your questions. This discussion group is going to close on 1st June. So
i strongly advise you not to post your question here. post it in this forum
instead. http://answers.microsoft.com/en-us/o...ult.aspx#tab=1

This is where I managed to get help from others to amend the VBA i gave you
to include secondary axis.



"Doug" wrote:

I tried this and works fine accept it changes the primary values. Is it
possible to have this change the secondary values instead?



"ck13" wrote:

Hi, tried psoting my reply but seems to have some problems. Press "Alt"+"F11"
to open the visual basic. Then at the left you will see VBAProject (Name of
your workbook"). Right click at that and insert a new module. Copy the code
into the new module and close VBA. Click on the chart that you want to change
and press "Alt"+"F8" to open the macros selection. Click on the appropriate
macros and run it.


"Doug" wrote:

Were do I need to put the macro? I tried placing it in the workbook and in
the sheet view code, but doesn't do anything. What am I doing wrong?
--
Thank you!


"ck13" wrote:

Hi,

I faced this problem and found a solution but you need to use macro.

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



Another way you can do is try this method by Jon Peltier
http://peltiertech.com/Excel/Charts/...nkToSheet.html

I have not tried his method but it should work fine as I have very good
experience using his other solutions to my charting problems.

"Doug" wrote:

When I have the verticle axis set to automatic on the Min & Max range it
usually shows a huge distance on the chart because it generally defaults to
0. If the data is 55-100, the chart will start at 0 at the bottom and the
55-100 will be toward the top scrunching the data. Seems like it should size
up the data to best fit everything when on the auto setting. I don't want to
use a fixed setting because if the data changes then I run into a similar
problem. Is there a way to either change the default settings so the charts
will automatically size themselves, or something else I can do to make this
work for me?
--
Thank you!