'Delete Method of series class failed' Error

[iepritchard]

Hi All,

I have an XY Scatter graph on a separate sheet "Chart2", and the data for this graph is on "Sheet3". I am trying to plot multiple series on the chart, but then also after making different choices deleting these series and plotting new ones. The code I have at the moment is...

Dim wsh As Worksheet
Dim serColl As SeriesCollection, ser As Series, rXValues As range
Dim lRowS As Long, lRowE As Long, lSer As Long
 
'START CHART
 
lRowE = 2 ' start in row 2  'Data starts in Cell B2 on sheet3
Set wsh = Worksheets("Sheet3")     
 
With Charts(1)
i = .SeriesCollection.Count
Do Until i < 1
.SeriesCollection(i).Delete
i = i - 1
Loop
End With 
 
'There is then some other code to delete the data from Sheet3 and replace it with the new data before plotting the new series below
 
' Add the series
    Do While wsh.range("B" & lRowE).Value <> ""  'Column B contains name of the series
        lRowS = lRowE
        Do While wsh.range("B" & lRowE + 1) = wsh.range("B" & lRowS)
            lRowE = lRowE + 1
        Loop
        Set rXValues = wsh.range(wsh.range("C" & lRowS), wsh.range("C" & lRowE)) 'column C contains the X axis data
        With serColl.NewSeries
            .XValues = rXValues
            .Values = rXValues.Offset(, 1)
            .Name = rXValues(1).Offset(, -1)
        End With
        lRowE = lRowE + 1
    Loop

Whenever I run this I get the 'Delete method of series class failed' and the .SeriesCollection(i).Delete code highlighted. I am very confused, and any help would be gratefully recieved!!

Thanks,
Ian

 

[Colin Legg]

Hi Ian,

See if the discussion on this link helps...
http://www.developersdex.com/vb/message.asp?p=2677&r=6558631


Also (not that I think this is the cause of the problem), it would be better to put Option Explicit at the top of your code module and declare the variable "i".

Hope that helps...