Match Graph Width to Inputed Data

[jjlafond]

Hello,

I am graphing (Line graph)a data series, with data going across the page in a single row.

I slowly add data to the end of this series, and an existing macro automatically expands the range of the graph to include the new data.

I would like the size of the graph to also expand automatically (VBA), and (roughly) line up with the data above it, so that the visual graph is directly beneath the data numbers.

Any ideas on how to do this?

 

[Comfy]

You'll need to experiment a little due to where you have your series' placed but this will get you going:

Sub ChartWidth()


Dim gWidth As Long
Dim mChart As Chart


gWidth = Range("G2:N2").Width


Set mChart = ActiveChart


mChart.ChartArea.Width = gWidth


End Sub

 

[Chris Mack]

Say your data spans columns A:E, and you have just updated it to be this way.

You can say:

Sub ResizeChart()
    n = Cells(1, Columns.Count).End(xlToLeft).Column
    ActiveSheet.Shapes("Chart 1").Width = Range(Cells(1, 1), Cells(1, n)).Width
End Sub

Hope this helps,

Chris.

P.S. What I'd really like to be able to do with this is to be able to use some kind of "ActiveShape" command. If anyone knows of a way of doing this I'd love to hear it.

 

[jjlafond]

Thanks for the responses! I took concepts from both to make it more efficient and user-friendly in my code. Its all working perfectly!

Width = Range(Range("C18"), Range("C18").End(xlToRight)).Width
ActiveSheet.Shapes("Chart1").Width = Width

 

[Chris Mack]

Yw.