Excell graphing question

[clarkmj <clarkmj@xxxxxxxxxx>]

Does anyone know how to do this.

Say I have a workbook with an unknown # of rows of data.

I determine that there are 30 sets ie..

 NumRows=count(A1:A1000)
 
Now that I know that I also have a chart that graphs these sets of data, but I do not want to keep going to the data source properties to tell excell the x-axis array value.  I do not want to leave it equal to a1:a1000 due to compressing the data to much but rather want to expand it to just equal the number of pairs.
I know that this is the line that will change the value but how do I make excell calculate it,  I have tried offset but get errors.

 ActiveChart.SeriesCollection(1).Values = "=Sheet1!A1:A#"

A# is the last in the data series

So my question is knowing how many rows off data you have, how do you set the series equal to that data?

Thanks for the help
Mike Clark
clarkmj@xxxxxxxxxx