Problem with Deleting Select Legend Keys

[nreynolds]

I trying to develop a macro that will populate a number of data points onto an Excel XY Scatter plot that already has two data series plotted. The macro currently works fine as far as loading the data points as a series and a few edits to position corresponding data labels. However, I would also like the legend to only show information on the two original data series. The subsequent data points should only be labeled on the chart using data labels....not the chart legend. Below is the code I'm trying to use for deleting the legend entries.

Private Sub ClearLegend()
Dim z As Integer
Worksheets("HGL vs Pipeline").ChartObjects("Pipeline Profile").Activate
For z = ActiveChart.SeriesCollection.Count To 3 Step -1
    ActiveChart.Legend.LegendEntries(z).Delete
Next
End Sub

It may also be important to note that on the same worksheet I have a macro-enabled button that clears the chart. The first time I populate the chart, the legend keys are deleted as anticipated. However, if I clear the chart and attempt the re-populate with the data points, I get the following error: "System Error &H80004005 (-2147467529). Unspecified error"

Perhaps, the issue lies somewhere in the chart clearing process? That coding is shown below.

Sub ClearChart()
  Dim i As Integer
    
  ActiveSheet.ChartObjects("Pipeline Profile").Activate
  ActiveChart.PlotArea.Select
  For i = ActiveChart.SeriesCollection.Count To 3 Step -1
    ActiveChart.SeriesCollection(i).Delete
  Next
End Sub

The idea is to allow the user to populate the chart with the certain data points. Then, if changes need to be made later, they can clear the chart and then display whatever new point information they wish...let me know if more information is needed.

[westconn1]

can you post a workbook with sample data?

[nreynolds]

Test.xlsx

Worksheet is attached. The current coding for this sheet is as follows:

Sub Profile()
    Application.ScreenUpdating = False
    ActiveSheet.ChartObjects("Pipeline Profile").Activate
    ActiveSheet.Activate
    
 Dim i As Integer, a As Integer, b As Integer
    
    a = 112
    b = 3
    
    For i = 67 To a
        If IsEmpty(ActiveSheet.Cells(i, 5).Value) Then
          Exit For
        Else
          ActiveChart.SeriesCollection.NewSeries
          ActiveChart.SeriesCollection(b).Name = ActiveSheet.Cells(i, 5).Value
          ActiveChart.SeriesCollection(b).XValues = ActiveSheet.Cells(i, 3).Value
          ActiveChart.SeriesCollection(b).Values = ActiveSheet.Cells(i, 4).Value
          ActiveChart.SeriesCollection(b).Select
          Selection.MarkerSize = 15
          Selection.Format.Line.Visible = msoFalse
          ActiveChart.SeriesCollection(b).ApplyDataLabels
          
          ActiveChart.SeriesCollection(b).DataLabels.Select
          Selection.ShowSeriesName = True
          Selection.ShowValue = False
          Selection.Position = xlLabelPositionBelow
          ActiveChart.SeriesCollection(b).Points(1).DataLabel.Select
          Selection.Format.TextFrame2.TextRange.Font.Size = 15
        End If
        b = b + 1
    Next
    
ActiveSheet.ChartObjects("Pipeline Profile").Activate
Dim z As Integer
For z = ActiveChart.SeriesCollection.Count To 3 Step -1
    ActiveChart.Legend.LegendEntries(z).Delete
Next
    
End Sub

Sub ClearChart()
  Dim i As Integer
    
  ActiveSheet.ChartObjects("Pipeline Profile").Activate
  ActiveChart.PlotArea.Select
  For i = ActiveChart.SeriesCollection.Count To 3 Step -1
    ActiveChart.SeriesCollection(i).Delete
  Next
End Sub

The user will essentially enter station, elevation, and description data in the table below the drawing sheet. The Profile button/macro should then load these as points onto the the chart above (the pipeline and ground data will probably not be loaded correctly since they would be entered on a differnet sheet). After populating these points, I'd like to legend to only show the two original lines (labled "Pipeline Invert" and "Ground Elevation"). The error pops up if I run the code more than once (clear chart and try to repopulate with editted data). As far as I can tell, the error has something to do with trying to delete legend entries that may have previously existed...not real sure though. Thanks for taking a look.

[Aflatoon]

Try this:

    Dim z                     As Integer
    With ActiveSheet.ChartObjects("Pipeline Profile").Chart.Legend.LegendEntries
        For z = .Count To 3 Step -1
            .Item(z).Delete
        Next
    End With

[nreynolds]

Works perfect. Thanks for helping out. Just for future knowledge, what was the issue with my original code?

[Aflatoon]

It was trying to delete legendentries that weren't there. It's always better to use the count of the collection you are deleting from as your control variable.

[Bart L]

The script removed all the time different legend entries, very randomly, also with the code from Aftaloon, but after endless attempts I found a solution.
I removed the legend and placed it back



ochart.Legend.Delete
ochart.SetElement (msoElementLegendRight)


With ochart.Legend.LegendEntries
For z = .Count - NumberOfSeries To NumberOfSeries + 1 Step -1
.Item(z).Delete
Next
End With


Now only the right legend entries are removed when I press my refresh button. I think some entries were not yet made before the removing started? Is that even possible?

I hope this helps some other people struggling with this in the future.