Chart adds extra category left and right

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Chart adds extra category left and right

Post by Jeff H »

In Excel 365, I've just started making charts in VBA. I've never used charts much, manually or with VBA, but it works great for my current needs.

Trouble is, if I create a chart for, say, January to December, the resulting chart includes a previous December on the left and a following January on the right with no data. Is there a way to eliminate those empty spaces so that the data starts and ends at the left and right borders of the chart?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Chart adds extra category left and right

Post by HansV »

What type of chart did you create?
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Chart adds extra category left and right

Post by Jeff H »

XY Scattering Lines

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Chart adds extra category left and right

Post by HansV »

You can set the minimum and maximum values of the x-axis.
Either double-click the x-axis, or click once on the x-axis, activate the Format tab of the ribbon and click Format Selection.
Click Axis Options under Axis Options.

S1509.png

You'll see numbers in the Minimum and Maximum boxes, but you can enter a date in them, e.g. 1/1/2022, and press Enter.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Chart adds extra category left and right

Post by Jeff H »

Perfect! Thanks Hans.

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Chart adds extra category left and right

Post by Jeff H »

One little glitch, though. I can't seem to get the date conversion right.
I publicly declared lStart and lEnd as Long.
In the userform that gets the date input, I use this to convert the string date into the serial number:

Code: Select all

xDate = CDate(Me.txtStart)
lStart = CLng(xDate)
So far so good.
But then in the calling macro I get an "Object doesn't support this property or method" error here:

Code: Select all

cht.Chart.ChartType = xlXYScatterLines
cht.Axes(xlCategory).MinimumScale = lStart
cht.Axes(xlCategory).MaximumScale = lEnd
I manually checked the serial numbers in the failed lines, and they are the correct dates.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Chart adds extra category left and right

Post by HansV »

I assume that cht is a ChartObject. If so, use

cht.Chart.Axes(xlCategory).MinimumScale = lStart
cht.Chart.Axes(xlCategory).MaximumScale = lEnd

just like you used cht.Chart in the line

cht.Chart.ChartType = xlXYScatterLines
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Chart adds extra category left and right

Post by Jeff H »

Of course. Sometimes I miss what's right in front of me. As always, many thanks!