Trying to set chart axis by actual data it is sorting through

[bdouglas1011]

Since I tried using the MIN & MAX but that does not seem to work since it returns zero for the axis even though it the values start at 10000. Is there a way to have the axis format based on the actual data it is sorting.

Ex : range is 10867 thru 19766

How can I get the range to format with MIN @ 10867 and max @ 19766

The data comes form Column C

Please help.

[noboffinme]

Hi,

It always helps to post the file you're working on to help explain your issue.

Can you post the file?

[bdouglas1011]

Please Login or Register  to view this content.

[noboffinme]

That helps, but can you also post the data file that the code applies to?

[bdouglas1011]

The file is huge I deleted alot of the data files usually my Rows are over 250,000

Thanks

[MrShorty]

It would seem to me that all you need to do is replace this:

Please Login or Register  to view this content.

with something other than scaleisauto=true. You can use the .minimumscale and .maximumscale properties to set the minimum and maximum values to whatever you deem appropriate http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx

[bdouglas1011]

I realize you can manually set it, I want it to automatically grab the range on the data it is using. I am sorting through 600,000 rows of data I don't know the range with out looking through the entire file that is why I would like the chart to do it when it creates itself.

[Andy Pope]

You can adapt something like this.

Please Login or Register  to view this content.

if you need to do more that 1 series, store the result of min and then compare to holding variable, updating as necessary. Then use that value to set scale minimum

[MrShorty]

Which is another way of saying that you don't like Excel's default algorithm for choosing axis limits and would like a different algorithm. Excel only knows the one algorithm, so, when you want something different, you have to write your own algorithm and apply it using the .minimumscale and .maximum scale properties.

You found the last row and stored it in the lLR variable and used it to set the ranges for the data series. You can do something very similiar using the MIN and MAX functions in VBA (http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx even uses the Min function in its example). Somewhere before you format the axis, you will need to use the Min and Max functions to find the minimum and maximum values of the desired range. Then you can apply those values to the minimumscale and maximumscale properties.