Chart adds extra category left and right

[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?

[HansV]

What type of chart did you create?

[Jeff H]

XY Scattering Lines

[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.

[Jeff H]

Perfect! Thanks Hans.

[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.

[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

[Jeff H]

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