Change Source Data in VBA


Risky Dave


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.
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.




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

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
ActiveChart.SetSourceData Source:=SourceRange, PlotBy:=xlRows
End With


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question