Chart not Sizing to larger range

Question

I recorded a macro and it resulted in the following code which worked as desired:

ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = "=Graph!$B$1"
ActiveChart.FullSeriesCollection(1).Values = "=Graph!$B$3:$B$170"
ActiveChart.FullSeriesCollection(1).XValues = "=Graph!$A$3:$A$170"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = "=Graph!$H$1"
ActiveChart.FullSeriesCollection(2).Values = "=Graph!$H$3:$H$367"
ActiveChart.FullSeriesCollection(2).XValues = "=Graph!$G$3:$G$367"

I built my code around that macro as such:

Dim LastRow As Long, LastRow1 As Long, LastRow2 As Long
Dim P1Rating As Range
Dim P2Rating As Range
Dim DateRange As Range
Dim ShName As String

With ActiveSheet
' gets the lengths of each row
LastRow1 = .Range("B" & .Rows.Count).End(xlUp).Row
LastRow2 = .Range("G" & .Rows.Count).End(xlUp).Row


' checks to see which one is longer    
    If LastRow1 >= LastRow2 Then
        LastRow = LastRow1
        Else
    LastRow = LastRow2
    End If

    ' assigns the longer count to each range
    Set P1Rating = .Range("B3:B" & LastRow)
    Set date1range = .Range("A3:A" & LastRow)
    Set P2Rating = .Range("H3:H" & LastRow)
    Set date2range = .Range("G3:G" & LastRow)
    ShName = .Name
End With

ActiveSheet.Shapes.AddChart2(227, xlLine, 600, 20).Select
indexofchart = ActiveChart.Parent.Index
ActiveSheet.ChartObjects(indexofchart).Activate
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = P1Name & " vs " & P2Name
ActiveChart.HasLegend = True
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = P1Name
ActiveChart.FullSeriesCollection(1).Values = P1Rating
ActiveChart.FullSeriesCollection(1).XValues = date1range
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = P2Name
ActiveChart.FullSeriesCollection(2).Values = P2Rating
ActiveChart.FullSeriesCollection(2).XValues = date2range

I am using : If LastRow1 >= LastRow2 Then LastRow = LastRow1 Else LastRow = LastRow2

to force using the largest range for all of the variables and verified the proper value is used. As you can see, I simply replaced the hard ranges from the macro with the variables Unfortunately, if date1range is a smaller range, then the graph stops at that point (Both series are shown 100% across the graph, but any data from series 2 past that point is lost) But in the recording, that series is only 167 long as well and the graph displays the whole 364 entries (the longer range). with the first series ending 1/3 of the way across the graph If that range is the larger one, then it displays as expected. Short of forcing the graph to use the longer range first, what can I do?

Answer 1

I found a simple and elegant solution that should work wonderfully. I found the answer here:https://peltiertech.com/plot-two-time-series-with-different-dates/

Simply put all date values into one column! | 9/15/2015 | 1400 | a | | | | | | | | 9/16/2015 | 1398 | a | | | | | | | | 9/16/2015 | 1399 | d | | | | | | | | 9/20/2015 | 1401 | b | | | | | | | | 9/15/2015 | | | 1400 | a | | | | | | 9/15/2015 | | | 1398 | c | | | | | | 9/20/2015 | | | 1406 | c | | | | | | 9/20/2015 | | | 1407 | d | | | | | | 9/20/2015 | | | 1408 | b | | | | |