Dynamic Ranges for Charts

[Kenneth Hobs]

Maybe this will give you some ideas.

Sub Macro1()
    Dim outstanding As Range
    Dim backlog As Range
Set outstanding = Sheet1.Range(Cells(2, 1), Cells(11, 1))
    Set backlog = Sheet1.Range(Cells(2, 2), Cells(11, 2))
ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(1).Values = outstanding
    ActiveChart.SeriesCollection(2).Values = backlog
End Sub

Sub Macro2()
    Dim outstanding As Range
    Dim backlog As Range
    Range("A1").Select 'Make sure that no object is selected.
    Set outstanding = ActiveSheet.Range("A2", Range("A" & Rows.Count).End(xlUp))
    Set backlog = ActiveSheet.Range("B2", Range("B" & Rows.Count).End(xlUp))
ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!" & outstanding.Address(True, True, xlR1C1)
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).Values = "=Sheet1!" & backlog.Address(True, True, xlR1C1)
    Range("A1").Select
End Sub