XL2002 - SeriesCollection.Index

[Trevor Williams]

I'm getting an error using the following code - seems like I can't use .Index
with the SeriesCollection... Is that right?

If so, what should I be using?

Thanks in advance

Trevor
---------------------------------------

ActiveSheet.ChartObjects("Chart 1").Activate

sCount = ActiveChart.SeriesCollection.Count
xCount = sCount / 2 + 1

For Each mySeries In ActiveSheet.ChartObjects("Chart
1").Chart.SeriesCollection
If mySeries.Index > xCount Then
mySeries.Delete
End If
Next

 

[Peter T]

Dim nSRcountOrig As Long, nSRreqd As Long

nSRcountOrig = ActiveChart.SeriesCollection.Count
nSRreqd = nSRcountOrig / 2 + 1

For i = nSRcountOrig To nSRreqd Step -1
ActiveChart.SeriesCollection(i).Delete
Next

Not sure which integers you want with "/2"

try this -

dim a as long, b as long
for i = 1 to 20
a = i / 2 +1
b = i \ 2 +1
debug.? i,a,b
next

Regards,
Peter T

 

[Trevor Williams]

Hi Peter

Thanks for the response - I originally tried using a loop that you have
supplied but hit problems as i went from the required series to the total
series... never thought about stepping backwards!

Am I right that you can't use .Index with the SeriesCollection?

Thanks again.

Trevor

 

[Peter T]

Am I right that you can't use .Index with the SeriesCollection?

..Index, a word preceded with a dot, is not a property but you could use
..Item(index-number)
With some types of collection you can return .Index as the index number of a
referenced item in the collection, though not with Series

When deleting items of any kind of collection, in this case series in a
Seriescollection, always loop backwards. If you particularly need to loop
forwards and you want to delete say items 4 to 6 -

for i = 1 to (6-4+1)
somecollection.Delete(4)
next

I can't think of an example why you would need to loop forwards but maybe
the above shows what's going on, ie each time you delete a given index all
subsequent decrement by one.

Regards,
Peter T