Excel VBA: How to Dynamically Update the Data Range of an Existing Chart

2025年12月29日

Overview

When creating reports in Excel, manually adjusting chart ranges every time the data volume increases or decreases is tedious. By utilizing the VBA SetSourceData method, you can update the specified cell range with a single click.

This article explains sample code and key points to realize smooth maintenance.

Sample Code — Resetting Data Range

Sub RefreshChartRange()
    Dim targetSheet As Worksheet        ' Sheet where the chart is located
    Dim chartObj   As ChartObject       ' Target chart object
    Dim srcRange   As Range             ' New data range

    ' 1. Get the target sheet
    Set targetSheet = ThisWorkbook.Worksheets("Report")          ' Sheet name is arbitrary

    ' 2. Set the data range to change (Assuming headers + values)
    Set srcRange = targetSheet.Range("C2:D15")                   ' Change as necessary

    ' 3. Update the data range of the specific chart
    Set chartObj = targetSheet.ChartObjects("RevenueChart")      ' Specify chart name
    chartObj.Chart.SetSourceData Source:=srcRange
End Sub

Code Explanation

Line	Role	Key Points
1-4	Variable Declaration	Managing `Worksheet` and `ChartObject` separately improves readability.
7	Sheet Acquisition	Change `Worksheets("Report")` to use this on a different sheet.
10	Range Specification	In cases where the data range expands or contracts dynamically, automatic detection using `End(xlUp)` or `CurrentRegion` is also possible.
13-14	Chart Update	`ChartObjects("RevenueChart")` identifies the chart by its Name property. Since default names (like “Chart 1”) are hard to distinguish, it is convenient to name charts when creating them.

FAQ

Q. What should I do if I don’t know the chart name?

A. The easiest way is to enumerate ChartObjects using a For Each loop in VBA and check the Name property in the Watch Window.

Q. Is there a way to automate this if the data range fluctuates every time?

A. By combining CurrentRegion, UsedRange, or getting the last row using Cells(Rows.Count, ColumnNumber).End(xlUp).Row, the chart will automatically expand even if rows are added.

Summary

Using the SetSourceData method allows you to instantly update existing chart data ranges via VBA.

By flexibly managing the sheet name, cell range, and chart name with variables, you can build a system to maintain multiple charts at once. Please utilize this as a first step to automate regular report creation and significantly reduce work time.

よかったらシェアしてね！