x-axis showing the 1. of the month

Hi people

I have been dealing with a problem for a loong time. It is about a graphs x-axis for stock closing prices. The data consists only of dates (col A) and a prices (col B). Need help with graph x-axis.xlsm

Sometimes there are no data for the 1st in the month. Sometimes there is. Also each data set for each month does not consist of the same number of days. E.g. one month can have 20 dates and prices another can have 18.

The space between the ticks (i think they are called) should therefore vary!

I would love an x-axis that shows the first day of the month (where data exits) for maybe each (each second/third) month.


Sub Chart()
Dim sht As Worksheet
Dim cht As Object
Dim chtrng As Range
Dim chtrngX, chtrngY As Range
Dim chttitle As String

Set sht = ThisWorkbook.Worksheets("Share Price")
Set chtrng = sht.Range(sht.Cells(Rows.Count, 1).End(xlUp), _
sht.Cells(1, Columns.Count).End(xlToLeft))
Set chtrngX = chtrng.Columns(1).Offset(1).Resize(chtrng.Rows.Count - 1)
Set cht = sht.ChartObjects.Add(Left:=35, Top:=45, Width:=400, Height:=150)

With cht.Chart
chttitle = "Stock"
.Parent.Name = chttitle
.ChartType = xlXYScatterLinesNoMarkers '#
.PlotVisibleOnly = False
.HasLegend = False

'##################################################
With .Axes(xlCategory, xlPrimary) ' x-axis
Dim xMin, xMax As Single
.HasTitle = False
'?
'.CategoryType = xlTimeScale
'.MajorUnitScale = xlMonths
‘?
‘Make sure x-axis starts at 1st in the month
xMin = DateSerial(Year(Application.WorksheetFunction.Min(chtrngX)), Month(Application.WorksheetFunction.Min(chtrngX)), 1)
xMax = DateSerial(Year(Application.WorksheetFunction.Max(chtrngX)), Month(Application.WorksheetFunction.Max(chtrngX)) + 1, 0) + 1
.MinimumScale = xMin
.MaximumScale = xMax
.TickLabels.NumberFormat = "dd-mmm-yy"
End With
'##################################################


With .Axes(xlValue, xlPrimary) 'y-axis
.HasTitle = False
End With

.Axes(xlCategory).TickLabels.Font.Size = 6
.Axes(xlValue).TickLabels.Font.Size = 6

Dim jSeries As Byte
For jSeries = 2 To chtrng.Columns.Count 'add variable no. of series from columns
With .SeriesCollection.NewSeries
.Values = chtrngX.Offset(, jSeries - 1)
.XValues = chtrngX
.Name = chtrng(1, jSeries)
.Format.Line.Weight = 0.8
.Border.Color = 131
End With
Next

With .ChartTitle
.Font.Size = 8
.Top = -1
.Text = chttitle
End With

With .PlotArea
.Top = 15
.Left = 12
.Height = 115
.Width = 500
End With
End With
End Sub



Would be so nice to have a good looking graph.!

Thanks in advance for all your replies!

/Damp