Update Chart Series Collection via VBA

[Chad Cameron]

Hi All,

I have about 50 charts. I recently deleted a bunch of rows to clear up old
data. In doing so, all my charts source data now reads K12:K70, instead of
K12:K500. I don't want to change them all by hand so I am trying to do it
with a marco as follows:

ActiveChart.SeriesCollection(1).Extend Source:=Worksheets("Monitoring Data
PRISM 401").Range("K12:K500")

My Chart has 3 seriescollections. My marco doesn't like the (1) after the
seriescollection. How can I specify the series to update?

(1) Y12:Y500
(2) Z12:Z500
(3) AB12:AB500

Thanks
Chad

 

[Don Guillett]

Why not use defined names which are self-adjusting
edit>name>define>name it colK>in the refers to box use an offset
=offset($k$11,1,0,counta($k:$k)-11),1)
now refer to that in the series =yourworkbookname!colk
do the same for the others.

 

[Chad Cameron]

I will try it,
Thanks

Why not use defined names which are self-adjusting
edit>name>define>name it colK>in the refers to box use an offset
=offset($k$11,1,0,counta($k:$k)-11),1)
now refer to that in the series =yourworkbookname!colk
do the same for the others.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Chad Cameron]

The problem is (unless there is a workaround), I have 3 tables based off of
1 data sheet, but I have 10-20 datasheets per spreadsheet. Now does this
mean I need to create a 'ColK' for each datasheet? [ColKsheet1]
[ColKsheet2], etc...

Chad

 

[Don Guillett]

Hard to tell without seeing.
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


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

The problem is (unless there is a workaround), I have 3 tables based off
of 1 data sheet, but I have 10-20 datasheets per spreadsheet. Now does
this mean I need to create a 'ColK' for each datasheet? [ColKsheet1]
[ColKsheet2], etc...

Chad

Why not use defined names which are self-adjusting
edit>name>define>name it colK>in the refers to box use an offset
=offset($k$11,1,0,counta($k:$k)-11),1)
now refer to that in the series =yourworkbookname!colk
do the same for the others.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Shane Devenshire]

Hi,

Now I know this will sound silly but since it sounds like you have only one
range with this problem but many charts:

1. Select rows K70:K500 and press Ctrl++ (control plus), pick Shift cells
down.
2. Select the last cell, which will have the data from row 70 and move it
back to row 70.

In the future, rather than choosing Edit, Delete try Edit, Clear contents to
maintain the reference but clear the data.