Hi,

Please be aware that I have also posted this in the XL Charts 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

You need to find the chart name. I added code to help you find the name.
You also have to get the range of address which a named SourceRange. Chart
object a very particular about activating before being able to change an
item. the code below is very simple and you should easily be able to
understand. Just don't question the format. Just accept this what is
required.

For Each chrt In ActiveSheet.ChartObjects
MsgBox (chrt.Name)
Set mychart = chrt
Next chrt
Set LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft)
Set SourceRange = Range(Range("A1"), LastCol)


With mychart
.Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=SourceRange, PlotBy:=xlRows
End With

"Risky Dave" wrote:

> Hi,
>
> Please be aware that I have also posted this in the XL Charts 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
>

you may not need VBA to resolve. Have a look at this site:

http://peltiertech.com/Excel/Charts/Dynamics.html
--
jb


"Risky Dave" wrote:

> Hi,
>
> Please be aware that I have also posted this in the XL Charts 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
>

Hi, I replied to this a few minutes ago but it hasn't appeared (I've got an
ongoing problem using this discussion group, perhaps to do with my using the
non-M/soft Firefox browser) so I'll repost in case. I think the best
solution is to name the ranges for your x-data and any y-series. Then you
can use these names in your chart series data itself and you won't actually
need VBA at all if you're careful to only insert new data within the range
names.

"Risky Dave" wrote:

> Hi,
>
> Please be aware that I have also posted this in the XL Charts 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
>