changing the source range of a chart

[MJKelly]

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 Guillett]

Sounds like a candidate for defined name ranges using offset
insert>name>define>name 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

 

[MJKelly]

Sounds like a candidate for  defined name ranges using offset
insert>name>define>name 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,

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

 

[Don Guillett]

=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 f5>goto>type in the name. Adjust as needed

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Sounds like a candidate for defined name ranges using offset
insert>name>define>name 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
message

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