[Excel VBA] How to Automatically Adjust Chart Position and Size to Fit a Cell Range

2025年12月27日

Introduction

When creating periodic reports, aligning charts manually by dragging them is time-consuming. By combining VBA’s ChartObjects properties with cell coordinates, you can align the position and size of your charts all at once.

This article explains the basic code and key points to fit a chart to a specific cell range.

Sample Code

Sub ResizeChartToRange()
    Dim ws As Worksheet          ' Target worksheet
    Dim layoutArea As Range      ' Destination cell range for the chart
    Dim chartObj As ChartObject  ' Target chart object

    ' 1. Specify worksheet and cell range
    Set ws = ThisWorkbook.Worksheets("Dashboard")     ' Change to your sheet name
    Set layoutArea = ws.Range("E3:J18")               ' Target area including headers

    ' 2. Get the chart and match position/size to the range
    Set chartObj = ws.ChartObjects("PerformanceChart")  ' Name the chart when creating it
    With chartObj
        .Top = layoutArea.Top
        .Left = layoutArea.Left
        .Width = layoutArea.Width
        .Height = layoutArea.Height
    End With
End Sub

Code Explanation

Step	Description	Key Point
1. Get Sheet and Range	Define the target worksheet and the specific range where the chart should go.	By changing `Range("E3:J18")`, you can easily apply this logic to other locations.
2. Get Chart and Place	Retrieve the chart object and apply the range’s dimensions.	Identifying the chart by name (e.g., `ChartObjects("PerformanceChart")`) improves code readability and maintenance.

Advanced Ideas

Dynamic Ranges: If the number of rows changes, use CurrentRegion or Cells(Rows.Count, ColNum).End(xlUp).Row to automatically expand the target cell range.
Multiple Charts: If you have multiple charts, you can manage chart names in an array or collection and use a loop to adjust their positions and sizes in one go.

Summary

By setting the Top, Left, Width, and Height properties to match a cell range, you can automate chart layout.

Managing sheet names, ranges, and chart names with variables increases reusability and significantly reduces the effort required to update materials. Please use this sample to streamline your regular reports and dashboard creation.

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