はじめに

Excelでグラフを作成したあと、表にデータを追加したのにグラフに反映されない、という経験はありませんか。グラフの参照範囲が固定されたままだと、新しく増えたデータが自動でグラフに表示されず、そのたびに手作業で範囲を修正する必要があります。

この記事では、SetSourceDataメソッドを使って、表のデータ量に合わせてグラフの参照範囲を自動的に拡張するマクロを紹介します。既存記事「グラフを自動で作成するVBAコード」の発展編として、すでにあるグラフを最新のデータ範囲に更新したい方に役立つ内容です。

この記事を読むと以下のことが分かります。

ChartObjectとChartオブジェクトの関係
SetSourceDataメソッドで参照範囲を更新する方法
最終行・最終列を自動取得して範囲を可変にする方法
データ追加時に自動でグラフを更新する仕組みの作り方

ChartObjectとChartの関係

シート上に配置したグラフは、ChartObjectという「グラフの入れ物」と、その中にあるChartという「グラフ本体」の2つのオブジェクトで構成されています。参照範囲の変更はChartオブジェクトのSetSourceDataメソッドを使って行います。

Sub ShowChartNames()
    Dim co As ChartObject
    For Each co In ActiveSheet.ChartObjects
        Debug.Print co.Name
    Next co
End Sub

上記のコードをイミディエイトウィンドウで実行すると、シート上にあるグラフの名前を確認できます。特定のグラフを操作したいときは、この名前を使ってActiveSheet.ChartObjects("グラフ 1")のように指定します。

SetSourceDataで参照範囲を更新する

グラフの参照範囲を更新する基本の書き方は以下の通りです。

Sub UpdateChartRange()
    Dim ws As Worksheet
    Dim co As ChartObject
    Dim lastRow As Long

    Set ws = ActiveSheet
    Set co = ws.ChartObjects(1)

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    co.Chart.SetSourceData Source:=ws.Range("A1:B" & lastRow)
End Sub

コードの解説

ws.Cells(ws.Rows.Count, "A").End(xlUp).Rowで、A列にデータが入力されている最終行を取得しています。これにより、データが何行追加されても最終行を自動で検出できます。
co.Chart.SetSourceData Source:=ws.Range(...)で、取得した最終行までの範囲を新しい参照元としてグラフに設定しています。
ws.ChartObjects(1)はシート上の1番目のグラフを表します。グラフが複数ある場合は、名前を指定するかインデックス番号を変えて対象を選んでください。

列も含めて自動拡張する

行だけでなく、集計項目（列）が増える可能性がある表では、最終列も一緒に取得すると、より汎用的なマクロになります。

Sub UpdateChartRangeWithColumn()
    Dim ws As Worksheet
    Dim co As ChartObject
    Dim lastRow As Long
    Dim lastCol As Long
    Dim rng As Range

    Set ws = ActiveSheet
    Set co = ws.ChartObjects(1)

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    co.Chart.SetSourceData Source:=rng
End Sub

ws.Cells(1, ws.Columns.Count).End(xlToLeft).Columnで1行目（見出し行）の最終列を取得し、ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))で表全体の範囲を組み立てています。表の行数・列数がどちらも変動する場合はこの方法が便利です。

データ変更時に自動で更新する

表を更新するたびに手動でマクロを実行するのは手間なので、Worksheet_Changeイベントと組み合わせて自動化しましょう。

' シートモジュールに記述
Private Sub Worksheet_Change(ByVal Target As Range)
    ' A列のデータが変更されたときだけ処理する
    If Intersect(Target, Me.Columns("A")) Is Nothing Then Exit Sub

    Dim co As ChartObject
    Dim lastRow As Long
    Dim lastCol As Long
    Dim rng As Range

    lastRow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row
    lastCol = Me.Cells(1, Me.Columns.Count).End(xlToLeft).Column

    Set rng = Me.Range(Me.Cells(1, 1), Me.Cells(lastRow, lastCol))

    For Each co In Me.ChartObjects
        co.Chart.SetSourceData Source:=rng
    Next co
End Sub

Intersect(Target, Me.Columns("A")) Is Nothingで、変更されたセルがA列に含まれるかどうかを判定しています。A列（データの1列目）に変更があった場合だけグラフを更新することで、無関係なセル編集のたびにグラフが再計算されるのを防いでいます。

注意点

SetSourceDataは見出し行を含む範囲を指定するのが基本です。見出し行を含めずにデータ範囲だけを指定すると、系列名が正しく表示されないことがあります。
Worksheet_Changeイベント内でシートの内容を書き換える処理を追加する場合は、無限ループを防ぐためにApplication.EnableEvents = Falseを使う必要があります（グラフ更新だけであればセルの値は変更しないため、今回のコードでは不要です）。
複数のグラフが同じ表を参照している場合は、For Each co In Me.ChartObjectsのようにループさせることで、すべてのグラフをまとめて更新できます。

まとめ

この記事では、SetSourceDataメソッドを使ってグラフの参照範囲を自動更新する方法を紹介しました。

Chart.SetSourceDataで参照範囲をコードから設定できる
End(xlUp)・End(xlToLeft)で最終行・最終列を取得すれば、データ量に応じた可変範囲を作れる
Worksheet_Changeイベントと組み合わせれば、表を更新するたびにグラフを自動更新できる

一度仕組みを作っておけば、データを追加するだけでグラフが自動的に最新の状態を反映するようになります。日々更新する集計表などにぜひ活用してみてください。

【ExcelVBA・マクロ】ChartObjectのデータ範囲を自動拡張する方法｜SetSourceDataでグラフを自動更新するマクロ【コピペOK】