グラフの参照範囲を変更する

【Excel 2003まで】
Sub Macro()
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D10")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

【Excel 2007以降】
Sub Macro()
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$D$10")
End Sub

Sub Sample()
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=Range("A1").CurrentRegion
End Sub

Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))

Range(Range("C1"), Cells(Rows.Count, 4).End(xlUp))

Set Target = Union(範囲A, 範囲B)

Set Target = Union(Range("B3:B7"), Range("B10:D12"))

Set Target = Union(項目軸ラベルのセル範囲, グラフデータのセル範囲)

Sub Sample()
    Dim Target As Range
    Set Target = Union(Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp)), _
                       Range(Range("C1"), Cells(Rows.Count, 4).End(xlUp)))
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=Target
End Sub

Sub Sample()
    Dim msg As String, i As Long
    With ActiveSheet.ChartObjects(1).Chart
        For i = 1 To .SeriesCollection.Count
            msg = msg & .SeriesCollection(i).Formula & vbCrLf
        Next i
    End With
    MsgBox msg
End Sub

Sub Sample()
    Dim 系列Aの数式 As String
    Dim A列の最終セル As String
    Dim A列の最終セルの1つ上 As String
    Dim B列の最終セル As String
    Dim B列の最終セルの1つ上 As String
    
    With ActiveSheet.ChartObjects(1).Chart
        系列Aの数式 = .SeriesCollection(1).Formula
        
        A列の最終セル = Cells(Rows.Count, 1).End(xlUp).Address
        A列の最終セルの1つ上 = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Address
        
        B列の最終セル = Cells(Rows.Count, 2).End(xlUp).Address
        B列の最終セルの1つ上 = Cells(Rows.Count, 2).End(xlUp).Offset(-1, 0).Address
        
        系列Aの数式 = Replace(系列Aの数式, A列の最終セルの1つ上, A列の最終セル)
        系列Aの数式 = Replace(系列Aの数式, B列の最終セルの1つ上, B列の最終セル)
        
        .SeriesCollection(1).Formula = 系列Aの数式
    End With
End Sub

Sub Sample()
    Dim i As Long, F As String, LastCell As Range
    Set LastCell = Cells(Rows.Count, 1).End(xlUp)
    With ActiveSheet.ChartObjects(1).Chart
        For i = 1 To .SeriesCollection.Count
            F = .SeriesCollection(i).Formula
            F = Replace(F, LastCell.Offset(-1, 0).Address, LastCell.Address)
            F = Replace(F, LastCell.Offset(-1, i).Address, LastCell.Offset(0, i).Address)
            .SeriesCollection(i).Formula = F
        Next i
    End With
End Sub

Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp))

Sub Sample()
    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
        .Formula = "=SERIES(" & Range("B1").Address & "," & _
                   Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp)).Address & "," & _
                   Range(Range("B2"), Cells(Rows.Count, 2).End(xlUp)).Address & "," & _
                   1 & ")"
    End With
End Sub

Sub Sample()
    Dim LastCell As Range
    Set LastCell = Cells(Rows.Count, 1).End(xlUp)
    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
        .Formula = "=SERIES(" & Range("B1").Address(External:=True) & "," & _
                   Range(Range("A2"), LastCell.Offset(0, 0)).Address(External:=True) & "," & _
                   Range(Range("B2"), LastCell.Offset(0, 1)).Address(External:=True) & "," & _
                   1 & ")"
    End With
End Sub

Sub Sample()
    Dim LastCell As Range, i As Long
    Set LastCell = Cells(Rows.Count, 1).End(xlUp)
    With ActiveSheet.ChartObjects(1).Chart
        For i = 1 To .SeriesCollection.Count
            .SeriesCollection(i).Formula = _
                "=SERIES(" & Cells(1, i + 1).Address(External:=True) & "," & _
                 Range(Cells(2, 1), LastCell.Offset(0, 0)).Address(External:=True) & "," & _
                 Range(Cells(2, i + 1), LastCell.Offset(0, i)).Address(External:=True) & "," & _
                 i & ")"
        Next i
    End With
End Sub