Last 90 days line graph without table

[Shruen]

Trying to create a line graph in an excel spreadsheet showing the last 90 days without using a table. Got one line graph which shows all data, but need one for the last 90 days. Is there a way to do this with offset or is there another method? Below is an example of some data.

 

[kanadaaa]

Try this in a standard module:

Function MonthNum(monthName As String) As Long
    MonthNum = Month(DateValue("1" & "/" & monthName & "/" & "2000"))
End Function

Sub SetChartDataWithin90Days()

    Dim date90 As String, newestYear As Range, newestMonth As Range, newestDay As Range, i As Long, startingRow As Long, searchMonth As Range, _
    searchYear As Range, searchLoop As Long, oldestDate As String, oldestDateMonth As String, oldestDateDay As String
    Set newestYear = Range("A" & Rows.Count).End(xlUp)
    Set newestMonth = Range("B" & Rows.Count).End(xlUp)
    Set newestDay = Range("C" & Rows.Count).End(xlUp)
    'Debug.Print newestDay & "/" & MonthNum(newestMonth.Value) & "/" & newestYear
    date90 = DateAdd("d", -90, newestDay & "/" & MonthNum(newestMonth.Value) & "/" & newestYear) 'Get date 90 days before the newest
    'Debug.Print date90
    
    'Get the oldest date within 90 days preceding the newest date
    For i = newestDay.Row To 2 Step -1
    
        'If in second or later loop reset ranges
        If Not searchMonth Is Nothing Then
            Set searchMonth = Nothing
        End If
        If Not searchYear Is Nothing Then
            Set searchYear = Nothing
        End If
        
        'Get the oldest month within 90 days preceding the newest date
        searchLoop = i
        Do
            If Not IsEmpty(Cells(i, "B")) Then
                Set searchMonth = Cells(i, "B")
            Else 'If month can't be obtained keep searching above row
                Set searchMonth = Cells(searchLoop, "B")
                searchLoop = searchLoop - 1
            End If
        Loop While IsEmpty(searchMonth)
        
        'Get the oldest year within 90 days preceding the newest date
        searchLoop = i
        Do
            If Not IsEmpty(Cells(i, "A")) Then
                Set searchYear = Cells(i, "A")
            Else 'If year can't be obtained keep searching above row
                Set searchYear = Cells(searchLoop, "A")
                searchLoop = searchLoop - 1
            End If
        Loop While IsEmpty(searchYear)
        
        'Check if a date in a row is within 90 days preceding the newest date
        oldestDateMonth = MonthNum(searchMonth.Value)
        oldestDateDay = Cells(i, "C")
        If Len(oldestDateMonth) = 1 Then
            oldestDateMonth = "0" & oldestDateMonth
        End If
        If Len(oldestDateDay) = 1 Then
            oldestDateDay = "0" & oldestDateDay
        End If
        oldestDate = oldestDateMonth & "/" & oldestDateDay & "/" & Right(searchYear, 2)
        If date90 > oldestDate Then
            startingRow = Rows(i + 1).Row
            Exit For
        End If
        'Debug.Print MonthNum(searchMonth.Value) & "/" & Cells(i, "C") & "/" & Right(searchYear, 2)
    Next i
    
    'If impossible to trace back to the past 90days
    If startingRow = 0 Then
        startingRow = 2
    End If
    'Debug.Print startingRow
    
    'Set chart data
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=Range(Cells(startingRow, "E"), Cells(newestDay.Row, "F"))
    ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues = Range(Cells(startingRow, "B"), Cells(newestDay.Row, "C"))
    
End Sub