Fit Chart Widths Automatically in VBA

KC305

New Member
Joined
Jan 24, 2014
Messages
1
Hi,

I can automate chart creation in VBA, but is there any way to also have it such that the x-axis of the chart begins with the minimum x-value and ends with the maximum x-value in the data set? Thanks!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
have a look at CEILING function, and use that to look at your range, and place a value on your spread sheet. I know there is a way to use that ceiling value when you build the chart in VBA, just can't think of it off the top of my head and the file I have it in is at work
 
Upvote 0
You probably don't want to use exactly the min and max of the data (unless the X value shows dates), but I'll get to that in a minute.

Figure out the min and max of the X values, then apply to the X axis:

Code:
Dim dXMin as Double, dXMax as Double
Dim vXVals as Variant
vXVals = ActiveChart.SeriesCollection(1).XValues
dXMin = WorksheetFunction.Min(vXVals)
dXMax = WorksheetFunction.Max(vXVals)

With ActiveChart.Axes(xlCategory)
    .MinimumScale = dxMin
    .MaximumScale = dXMax
End With

To get nicer axis scale parameters than simply using the min and max, check out my tutorial Calculate Nice Axis Scales in Excel VBA.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top