Solved: Excel Range for Chart

[Kindly_Kaela]

Hi Everyone!

I'm attempting to create a pie chart in Excel. Here's the macro code that did it...



ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Industry Total").Range( _
"A1:A23,D1:D23"), PlotBy:=xlColumns



Here's my problem. It's unknown how many rows will be part of the pie chart. It could change daily. In the code above, the pie chart graphs out 23 rows. Lets say the data only filled 5 rows (instead of 23). What code should I use to have the macro figure out how many rows are used, and then draw up a pie chart with that many rows only.

Thanks in advance for your help!
Kaela

[Bob Phillips]

Why don't you use dynamic range names for those ranges, and use something like



ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Industry Total").Range( _
"Names,Amounts"), PlotBy:=xlColumns

[Kindly_Kaela]

I dont understand?

:cloud9:

[Andy Pope]

To expand on Bob's idea.

Have a read of this on how to create and name a range with variable content.
http://www.contextures.com/xlNames01.html

You can then use the names in your code as Bob has done.

Or have a read of this if you want your chart to update without code.
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html