Sub Plot_Dynamic_Graph_Range()
'
' Plot_Dynamic_Graph_Range Macro
'
'Set the type of parameters
Dim LastRow As Long 'Define the last row address
Dim StartDate As Date 'Define the start date for plotting purpose
Dim EndDate As Date 'Define the end date for plotting purpose
Dim FirstRow As Long 'Define the first non empty row of the chart
Dim EndRow As Long 'Define the last non empty row of the chart
Dim Duration As String 'Define the range of plotting
Dim S As Worksheet
'---
Set S = Sheets("Avec VBA")
LastRow = S.Range("A:A").SpecialCells(xlCellTypeLastCell).Row 'Define the last non empty row number
StartDate = S.Range("L2")
EndDate = DateAdd("m", S.Range("L4"), StartDate) - 1 'Calculate the end date as an addition of the plotting duration to the start date
For Row = 2 To LastRow
If S.Cells(Row, 1) >= StartDate Then FirstRow = Row: Exit For 'Iterative check if the first encountered row is greater than or equal to the start date of the plotting window
Next
For Row = LastRow To 2 Step -1
If S.Cells(Row, 1) <= EndDate Then EndRow = Row: Exit For 'Iterative check if the last encountered row is less than or equal to the end date of the plotting windows
Next
'/// Les graphiques ///
Duration = S.Range("A" & FirstRow & ":A" & EndRow & "," & "B" & FirstRow & ":B" & EndRow).Address
S.ChartObjects("Graphique 2").Chart.SetSourceData Source:=S.Range(Duration), PlotBy:=xlColumns
'---
Duration = S.Range("A" & FirstRow & ":A" & EndRow & "," & "C" & FirstRow & ":C" & EndRow).Address
S.ChartObjects("Graphique 3").Chart.SetSourceData Source:=S.Range(Duration), PlotBy:=xlColumns
End Sub