Automatically Changing Excel Chart Data Sorce

[Stevie D]

Hi I have a chart in excel based on the data in two non contiguous columns
(column C and column G) in a different worksheet (Source_Data) in the same
workbook.

The number of rows in the Source_Data worksheet will vary.

My question is: how can I automatically change the chart data source to take
into account the different number of rows in theses two columns?

 

[David F]

Here's one approach:

1. Use a for/next loop.
2. Set the beginning parameter to the first row of Source_Data.
3. Upon every loop check to see if there is data in that row of column
C.
4. When there is not, exit the loop.
5. Use the counter value-1 for the row value of the range.

Of course the final range will include a variable for the row value.
Hope these clues help.

David F.

 

[Stevie D]

Thanks for the advice David, but once I have passed the number of rows to a
varaiable (say NumRows) how do I use the variable to reset the charts
datasource to include that cell reference as the end of the datarange.