changing the source range of a chart
 

Hi,

I want to be able to plot data on a radar chart, however, the number
of points varies from around 5 to 15. How can I use VBA code to reset
the source data? The source data is in a column, and I want to use
the first row to the last row (it's the last row that changes
according to the amount of data I need to chart).

Basically, I need to know how to adjust the source range using VBA?
The size of the range would be the same for the three series I plot on
the radar chart.

Hope you can help?

Thanks,
Matt

changing the source range of a chart
 

Sounds like a candidate for defined name ranges using offset
insertnamedefinename it colA or whatever.
In the refers to box
=offset($a$1,0,0,counta($a:$a),1)
in the source of the chart type in =yourworkbookname.xls!cola
Now when you add or delete from col a the range will self adjust

to add col B tied to col A length
=offset(cola,1)
etc

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MJKelly" wrote in message
...
Hi,

I want to be able to plot data on a radar chart, however, the number
of points varies from around 5 to 15. How can I use VBA code to reset
the source data? The source data is in a column, and I want to use
the first row to the last row (it's the last row that changes
according to the amount of data I need to chart).

Basically, I need to know how to adjust the source range using VBA?
The size of the range would be the same for the three series I plot on
the radar chart.

Hope you can help?

Thanks,
Matt

changing the source range of a chart
 

On May 11, 7:43*pm, "Don Guillett" wrote:
Sounds like a candidate for *defined name ranges using offset
insertnamedefinename it colA or whatever.
In the refers to box
=offset($a$1,0,0,counta($a:$a),1)
in the source of the chart type in =yourworkbookname.xls!cola
Now when you add or delete from col a the range will self adjust

to add col B tied to col A length
=offset(cola,1)
etc

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"MJKelly" wrote in message

...

Hi,

I want to be able to plot data on a radar chart, however, the number
of points varies from around 5 to 15. *How can I use VBA code to reset
the source data? *The source data is in a column, and I want to use
the first row to the last row (it's the last row that changes
according to the amount of data I need to chart).

Basically, I need to know how to adjust the source range using VBA?
The size of the range would be the same for the three series I plot on
the radar chart.

Hope you can help?

Thanks,
Matt

Don,

When I try this I end up with a range which is three cells longer than
required. I only need the range to be as long as the column of data
(the data is continuous).
Can the source range be changed using VBA? I need to change the
source of three series of data for the same chart (all ranges will be
the same size, just in different columns).
Regards,
Matt

changing the source range of a chart
 

=offset($a$1,0,0,counta($a:$a),1)
=offset($a$1,0,0,counta($a:$a)-3,1)
or play around till you get what you want.
=offset($a$1,3,0,counta($a:$a),1)
to check what is shown use f5gototype in the name. Adjust as needed

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MJKelly" wrote in message
...
On May 11, 7:43 pm, "Don Guillett" wrote:
Sounds like a candidate for defined name ranges using offset
insertnamedefinename it colA or whatever.
In the refers to box
=offset($a$1,0,0,counta($a:$a),1)
in the source of the chart type in =yourworkbookname.xls!cola
Now when you add or delete from col a the range will self adjust

to add col B tied to col A length
=offset(cola,1)
etc

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"MJKelly" wrote in
message

...

Hi,

I want to be able to plot data on a radar chart, however, the number
of points varies from around 5 to 15. How can I use VBA code to reset
the source data? The source data is in a column, and I want to use
the first row to the last row (it's the last row that changes
according to the amount of data I need to chart).

Basically, I need to know how to adjust the source range using VBA?
The size of the range would be the same for the three series I plot on
the radar chart.

Hope you can help?

Thanks,
Matt

Don,

When I try this I end up with a range which is three cells longer than
required. I only need the range to be as long as the column of data
(the data is continuous).
Can the source range be changed using VBA? I need to change the
source of three series of data for the same chart (all ranges will be
the same size, just in different columns).
Regards,
Matt