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

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

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

=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