Modify =SERIES formula to always depict last ROW in column which is not zero?

[ScubaGuy]

How do I modify this formula (see below) so that the last ROW which contains a number (non-zero) is automatically plotted in the graph (which this =SERIES is derived from)?

=SERIES('Sheet1'!$D$2,'Sheet1'!$A$3:$A$62,'Sheet1'!$D$3:$D$62,1)

In this case, "62" is my last row which now contains a number. I want to replace "62" with some formula code so it is automatically found.

[Andy Pope]

If you are using vba then you need to dechiper the series formula, update the relevant pieces and then reapply new formula.

if you know the end row then you might be able to get away with

activechart.SeriesCollection(1).formula=replace(activechart.SeriesCollection(1).formula,"62","63")

[ScubaGuy]

I used this in a loop to accomplish my mission over the multitude of sheets. Problem is now, one of the graphs I am using it on has TWO series on it. This is only updating one of them. How do I modify the formula so that it updates both please?

Thanks,
Jim

[Andy Pope]

loop through the series collection,

for lngIndex = 1 to activechart.seriescollection.count
   activechart.SeriesCollection(lngindex).formula=replace(activechart.SeriesCollection(lngindex).formula,"62","63")
next

[ScubaGuy]

Your Loop worked like a charm. THANK YOU. Now, is there any way to substitute the "62","63" with code that automatically finds the last row with a number in it and increases it row+1?

[Andy Pope]

one way

lastrow=sheet1.cells(sheet1.rows.count,1).end(xlup).row

[ScubaGuy]

Yes, but how do I add that code into the chart's "=SERIES.." formula please, so the line chart automatically adjusts to add the next empty row?

[Andy Pope]

lastrow=sheet1.cells(sheet1.rows.count,1).end(xlup).row
for lngIndex = 1 to activechart.seriescollection.count
   activechart.SeriesCollection(lngindex).formula=replace(activechart.SeriesCollection(lngindex).formula,lnglastrow-1,lnglastrow)
next

[ScubaGuy]

You da man! Thank you kindly!

Jim