Prev: Charting
Next: legend problem in mixed line-scatter graph Excel 2007
From: Risky Dave on 9 Nov 2009 09:24
Hi,

Please be aware that I have also posted this in the XL Programming group as
I am not sure which is most appropriate.

I have a chart set up (eg a simple bar chart) that I want to be able to
update automatically using VBA. The source data will vary in quantity and I
have no way of knowing in advance how many data there will be.
Eg
I might currently have:
A1 B1 C1 D1
10 12 15 35

but when I want to refresh the chart I might have:
A1 B1 C1 D1 E1 F1
15 5 10 20 25 10

(don't worry about the actual values).

How do I tell Excel to re-size the source data area automatically (ie as
part of a VBA macro) - the number of data may reduce as well as increase?

I'm reasonalby comfortable with VBA but by no means am I a programmer, so a
brief explanation of how the solution works would be appreciated.

This is in Office 2003 if that makes a difference.

TIA

Dave

From: Luke M on 9 Nov 2009 09:38
Before tackling this process using VBA, would it be possible to solve with a
dynamic range?
http://peltiertech.com/Excel/Charts/Dynamics.html

In your scenario, perhaps a range defined something like:
=OFFSET($A$1,0,0,COUNT(1:1),1)
named "MyRange"

In your chart, the data range is:
='My Book.xls'!MyRange



--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Risky Dave" wrote:

> Hi,
>
> Please be aware that I have also posted this in the XL Programming group as
> I am not sure which is most appropriate.
>
> I have a chart set up (eg a simple bar chart) that I want to be able to
> update automatically using VBA. The source data will vary in quantity and I
> have no way of knowing in advance how many data there will be.
> Eg
> I might currently have:
> A1 B1 C1 D1
> 10 12 15 35
>
> but when I want to refresh the chart I might have:
> A1 B1 C1 D1 E1 F1
> 15 5 10 20 25 10
>
> (don't worry about the actual values).
>
> How do I tell Excel to re-size the source data area automatically (ie as
> part of a VBA macro) - the number of data may reduce as well as increase?
>
> I'm reasonalby comfortable with VBA but by no means am I a programmer, so a
> brief explanation of how the solution works would be appreciated.
>
> This is in Office 2003 if that makes a difference.
>
> TIA
>
> Dave
>
 | 
Pages: 1
Prev: Charting
Next: legend problem in mixed line-scatter graph Excel 2007