Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Deleting all the data series in a dynamic chart

Tags:

excel

vba

charts

I create a dynamic chart on multiple data series according to the selection I make in the listbox (Lbox1).

It throws an error when I delete all the data series initially to make a fresh chart.

Public Sub listbox_selection()
    Dim i As Integer
    Dim temp As String
    Dim k As Integer
    Dim s As SeriesCollection

    k = Sheets("Plan1").ChartObjects(1).Chart.SeriesCollection.count

    ##This part giving error
    For i = 1 To k
        Sheets("Plan1").ChartObjects(1).Chart.SeriesCollection(i).Delete
    Next
    ####

    Sheets("Plan1").ListBoxes("LBox1").Select
    For i = 1 To Sheets("Plan1").Shapes("LBox1").ControlFormat.ListCount
        If Sheets("Plan1").ListBoxes("LBox1").Selected(i) = True Then
            Call Listit(X:=i)
        End If
    Next
End Sub

Public Sub Listit(ByVal X As Integer)
    X = X + 3
    With Sheets("Plan1").ChartObjects(1).Chart
        With .SeriesCollection.NewSeries
           .XValues = Range("Q2:U2")
           .Values = Range("Q" & X & ":U" & X & "")
           .Name = Range("P" & X).Value
        End With    
    End With
End Sub
like image 901
Alvi John Avatar asked Oct 17 '12 18:10

Alvi John


People also ask

How do you remove the data series from a chart?

To remove a chart's data series, click “Chart Filters” and then click “Select Data.” Select the series in the Legend Entries (Series) box, and then select “Remove.” Click “OK” to update the chart.

How do you get rid of a series in a chart in Excel?

Click anywhere in your chart. next to the chart. On the Values tab, check or uncheck the series or categories you want to show or hide. Click Apply.


2 Answers

Try this,

Sub temp()
  ActiveSheet.ChartObjects("Chart 1").Activate
  For Each s In ActiveChart.SeriesCollection
      s.Delete
  Next s
End Sub
like image 74
nightcrawler23 Avatar answered Sep 18 '22 17:09

nightcrawler23


You can use Sheets("Plan1").ChartObjects(1).Chart.ChartArea.ClearContents

like image 21
3alster Avatar answered Sep 19 '22 17:09

3alster