Time series values not matching the horizontal axis on my Excel chart

Question

SOLVED BY USING X-Y PLOT INSTEAD OF LINE CHART

I want to create a chart in Excel that contains multiple series that are linked in terms of date (i.e. one series ends on 2018/12/31 the next begins 2018/12/31). The raw data is taken from a sheet with two columns; the date column and the value column.

The chart in question here is called 'Beta' and contains 3 time series. This is my attempt:

Sub UpdateGraph
Dim ws             As Worksheet
Dim rng_NF3        As Range
Dim rng_Barra      As Range
Dim rng_NF3_Date   As Range
Dim rng_Barra_Date As Range
Dim rng_Total_Date As Range
Dim rng_Total_Val  As Range
Dim cht_Name       As String
Dim ArrDate        As Variant
Dim arrValues      As Variant


        cht_Name = "Beta"  ' Example
        Set ws = Activesheet
        Set cht = ws.ChartObjects(cht_Name)
        Set rng_NF3 = ws.Range(ws.Cells(2, 4), ws.Cells(200, 4)) ' Set range of values from NF3 (GEM3)
        Set rng_Barra = ws.Range(ws.Cells(201, 4), ws.Cells(500, 4)) ' Set range of values Barra
        Set rng_NF3_Date = rng_NF3.Offset(0, -1) ' Set range of date for NF3 observations
        Set rng_Barra_Date = rng_Barra.Offset(0, -1) ' Set range of date for Barra observations
        Set rng_Total_Date = Union(rng_NF3_Date, rng_Barra_Date)
        Set rng_Total_Val = Union(rng_NF3, rng_Barra)
        ArrDate = rng_Total_Date.Value
        arrValues = rng_Total_Val.Value
        With cht.Chart
            .FullSeriesCollection(1).Format.Line.ForeColor.RGB = ws.Cells(1, 20).Interior.Color
            .FullSeriesCollection(2).Format.Line.ForeColor.RGB = ws.Cells(2, 20).Interior.Color
            .FullSeriesCollection(1).Values = rng_NF3 ' Value series for NF3
            .FullSeriesCollection(2).Values = rng_Barra ' Value series for Barra
            .FullSeriesCollection(1).XValues = rng_NF3_Date
            .FullSeriesCollection(2).XValues = rng_Barra_Date
             If cht_Name = "Beta" Then ' Defining Beta = 1
             .FullSeriesCollection(3).Format.Line.ForeColor.RGB = ws.Cells(1, 21).Interior.Color '  Color
             .FullSeriesCollection(3).Values = 1 ' Should be a range with the same number in all elements, not working but another issue.
             .FullSeriesCollection(3).XValues = rng_Total_Date
             End If
             ' X-axis
             With cht.Chart.Axes(xlCategory)
                .CategoryType = xlTimeScale
                '.MajorUnitScale = xlMonths
                .MinimumScale = ArrDate(LBound(ArrDate, 1), 1)
                .MaximumScale = ArrDate(UBound(ArrDate, 1), 1)
                .MajorUnit = 3 ' Manually set x-axis unit scale
                '.MajorUnitIsAuto = True
             End With
            ' Y-axis
            With cht.Chart.Axes(xlValue)
                .MinimumScaleIsAuto = True
                .MaximumScaleIsAuto = True
                .MajorUnitIsAuto = True
            End With
        End With

End sub

I wanted the series to continue after each other but instead they both begin at the first date of the horizontal axis. I suspect Excel does not recognize my attempt to match the date of my time series to the date of the x-axis.

Here is how my figure looks. I am using a line chart.

Answer 1

Answer provided by @TimWilliams, if he doesn't create a post I will mark this as the answer when possible.

The solution is to use X-Y scatter plot (line option available) instead of the line chart I originally used.