Dynamic Chart & VBA

[kennyx]

I've got a table of data in a worksheet called "Charts"... the table starts in cell "B2" and the rest changes (dynamic chart).

ActiveChart.SetSourceData Source:=Sheets("Charts").Range("B2" & ":" & "Z" & numColumns), PlotBy _
        :=xlColumns

My problem is "Z" needs to be dynamic, as the number of rows will constantly change. What's the best way to tackle this?

thanks.

 

[hatepoppy]

what kind of dynamic chart are you looking for? xy lines, bars, etc?

 

[Oaktree]

Sounds like a job for a dynamic named range.

See http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html for one example

 

[kennyx]

what kind of dynamic chart are you looking for? xy lines, bars, etc?

They're xy lines... basically a table of historical Fund returns. Each column is a different fund, and each row is a different month.

As for dynamic charts (the solution without using VBA), for those to work you need to have a pre-existing table first... of which mine does not.

 

[Oaktree]

What do you mean "you need to have a pre-existing table first"?

If you're hardcoding B2 as the upper-left corner in your table with your code, that's all the anchor you'd need for a dynamic named range as well...