How to delete the empty series on a legend?

dboone25

Board Regular
Joined
May 8, 2015
Messages
185
hi, I am having a few difficulties using the xlXYScaller graph in where it adds in three blank series into the graph. Unsure how this works but this series indicates in the legend and looks a bit of a mess.

I have been trying to use this code:

HTML:
Dim c As Chart: Set c = activechart
Dim i As Integer
Dim a As Variant
c.HasLegend = False
c.HasLegend = True
n = 0 'Counter
For i = 1 To 6
a = c.SeriesCollection(i).Values
If WorksheetFunction.Sum(a) = 0 Then
c.Legend.LegendEntries(i-n).Delete 'Added in Counter
n = n+1 'Counter
End If
Next i

It gives me an error under '
HTML:
If Worksheetfunction.Sum(a) = 0
'Run-time error '13': Type mismatch


Not sure how to get around this or perhaps there is an easier way, thank you.
 
Many thanks for your help but keep on getting an error under:

Code:
c.HasLegend = False
          c.Haslegend = True


The error is comes up with Run-time error '91'
Object variable or With block variable not set

Kind regards
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
What have you changed since you posted this question?

The first post says you received an error on this line:

Code:
If Worksheetfunction.Sum(a) = 0

Which implies that you were able to get past the lines you are now receiving an error on (c.HasLegend).

So you must have changed something if your code was first able to get to the sum line and now is not able to get past the HasLegend lines.

If you are stepping through this macro and the chart is not active, then you are going to have issues because you're setting c to "ActiveChart".

Hope that makes sense.
 
Upvote 0
My apologies...it was a typo on my end...

Double checked everything and debugged the code again. it has come up with the original error on:

Code:
If WorksheetFunction.sum(a) = 0 Then

Run-time error '13' Type mismatch
 
Upvote 0
I think I found the issue, try this:

Code:
Private Sub CommandButton3_Click()
Dim rXVals As Range
Dim rYVals As Range
 
Dim bXVals As Range
Dim bYVals As Range
 
Dim LastRow As Long
Dim Name As Range
 
With Worksheets("Sheet2")
    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    
    If LastRow < 41 Then
        MsgBox "No data found.", vbInformation
        Exit Sub
    End If
   
    Set rXVals = .Range("C41:C" & LastRow)
    Set rYVals = .Range("I41:I" & LastRow)
End With
   
With Worksheets("Sheet2")
    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    
    If LastRow < 41 Then
        MsgBox "No data found.", vbInformation
        Exit Sub
    End If
   
    Set bXVals = .Range("C41:C" & LastRow)
    Set bYVals = .Range("J41:J" & LastRow)
End With
   
With ActiveSheet.ChartObjects.Add(Left:=30, Width:=650, Top:=20, Height:=375).Chart
    .ChartType = xlXYScatter
    
    With .SeriesCollection.NewSeries
        .Name = "=Sheet2!$E$41" ' optional
        .XValues = rXVals
        .Values = rYVals
        '.bYValues = bYVals
    End With
End With

ActiveSheet.ChartObjects(1).Activate
   
'DELETE EMPTY SERIES

Dim c As Chart: Set c = ActiveChart
Dim i As Integer
Dim a As Variant

c.HasLegend = False
c.HasLegend = True

N = 0 'counter

For i = 1 To 6
    a = c.SeriesCollection(i).Values

    If WorksheetFunction.Sum(a) = 0 Then
        c.Legend.LegendEntries(i - N).Delete ' add in counter
        N = N + 1 'counter
    End If
Next i
End Sub

1. Your code tabbing was all kinds of jacked up; I've gone through and fixed that.
2. You originally had this:

Code:
    With ActiveSheet.ChartObjects.Add(Left:=30, Width:=650, Top:=20, Height:=375).chart
    .ChartType = xlXYScatter
    
    With .SeriesCollection.NewSeries

**a whole bunch of code**

        .Name = "=Sheet2!$E$41" ' optional
        .XValues = rXVals
        .Values = rYVals
        '.bYValues = bYVals
    End With
   
  
End With

So the chart wasn't getting created (or at least not fully created) before you were trying to go through your delete empty series code. Changed it to:

Code:
With ActiveSheet.ChartObjects.Add(Left:=30, Width:=650, Top:=20, Height:=375).Chart
    .ChartType = xlXYScatter
    
    With .SeriesCollection.NewSeries
        .Name = "=Sheet2!$E$41" ' optional
        .XValues = rXVals
        .Values = rYVals
        '.bYValues = bYVals
    End With
End With

**a whole bunch of code**

3. I also added this line after the chart gets created:

Code:
ActiveSheet.ChartObjects(1).Activate

The only issue with this is if you have more than one chart displayed on the active sheet, the first one will always get activated. Hopefully you don't have more than one chart.

With the full code I provided above, I am getting the following error:

Run-time error '104':
Inavlid Parameter

Offending line: a = c.SeriesCollection(i).Values

Value of i is 2, which means it didn't get this error going the first time it went through the For/Next loop.

If I add "Debug.Print c.SeriesCollection.Count", the result is 1, which is why it's failing when i = 2.

This is probably stemming from the fact that my test data is not the same as your actual data, and it probably isn't in the same location either.
 
Last edited:
Upvote 0
Using the full code I provided above, try changing your delete empty series section from:

Code:
'DELETE EMPTY SERIES

Dim c As Chart: Set c = ActiveChart
Dim i As Integer
Dim a As Variant

c.HasLegend = False
c.HasLegend = True

N = 0 'counter

For i = 1 To 6
    a = c.SeriesCollection(i).Values

    If WorksheetFunction.Sum(a) = 0 Then
        c.Legend.LegendEntries(i - N).Delete ' add in counter
        N = N + 1 'counter
    End If
Next i

To:

Code:
'DELETE EMPTY SERIES

Dim c As Chart: Set c = ActiveChart
Dim i As Integer
Dim a As Variant
Dim isEmptySeries As coolean

c.HasLegend = False
c.HasLegend = True

For i = 1 To c.SeriesCollection.Count
    .SeriesCollection(i).ApplyDataLabels Type:=xlDataLabelIsShowValue, AutoText:=True, LegendKey:=False
    isEmptySeries = True
    
    For j = .SeriesCollection(i).Points.Count To 1 Step -1
        If .SeriesCollection(i).Points(j).DataLabel.Text = 0 Then
            .SeriesCollection(i).Points(j).HasDataLabel = False
        Else
            isEmptySeries = False
        End If
    Next j
    
    If isEmptySeries Then
        .SeriesCollection(i).Delete
    End If
Next i

This code is untested, but I got it from here: https://stackoverflow.com/questions/7875326/deleting-empty-series-out-of-graph-with-vba

Obviously, as I alluded to in my previous post, it's a little difficult for me to test the code because my data doesn't match yours.
 
Upvote 0
woops, few typos...try this:

Code:
'DELETE EMPTY SERIES

Dim c As Chart: Set c = ActiveChart
Dim i As Integer
Dim a As Variant
Dim isEmptySeries As Boolean

c.HasLegend = False
c.HasLegend = True

For i = 1 To c.SeriesCollection.Count
    c.SeriesCollection(i).ApplyDataLabels Type:=xlDataLabelIsShowValue, AutoText:=True, LegendKey:=False
    isEmptySeries = True
    
    For j = c.SeriesCollection(i).Points.Count To 1 Step -1
        If c.SeriesCollection(i).Points(j).DataLabel.Text = 0 Then
            c.SeriesCollection(i).Points(j).HasDataLabel = False
        Else
            isEmptySeries = False
        End If
    Next j
    
    If isEmptySeries Then
        c.SeriesCollection(i).Delete
    End If
Next i
 
Upvote 0
Many thanks for this..just about to run a test in a bit but as im going through the code I noticed where does the integer j come from?

Code:
[COLOR=#333333]j = .SeriesCollection(i).Points.Count To 1 Step -1[/COLOR]

Does that need to be defined first?
 
Upvote 0
You don't need to define anything unless you're using Option Explicit. Assuming you are using Option Explicit, just dim it.

Code:
Dim c As Chart: Set c = ActiveChart
Dim i As Integer, j As Integer
Dim a As Variant
Dim isEmptySeries As Boolean
 
Upvote 0
Funny this time stepping into the code it comes back with the other error run-time error '91': Object variable or With block variable not set

This is the whole code again. Maybe it placing it in the wrong part of it...really not sure about this anymore...little frustrating...I do thank you for all the time you have helped me out with this..there has to be a solution.


Code:
Private Sub CommandButton3_Click()
 
Dim rXVals As range
Dim rYVals As range
Dim i As Long
Dim LastRow As Long
Dim Name As range
 
' If no data is found show error message box
With Worksheets("Sheet2")
    LastRow = .Cells(.Rows.count, "C").End(xlUp).Row
    If LastRow < 41 Then
        MsgBox "No data found.", vbInformation
        Exit Sub
    End If
   
    'select columns C,I and J
    Set rXVals = .range("C41:C" & LastRow)
    Set rYVals = .range("I41:J" & LastRow)
   
    End With
           
    ' place chart in location, choose chart type and plot data series
    With ActiveSheet.ChartObjects.Add(Left:=30, Width:=650, Top:=20, Height:=375).chart
    .ChartType = xlXYScatter
    For i = 1 To rYVals.Columns.count
    With .SeriesCollection.NewSeries
      
   
    'Delete empty series
    Dim c As chart: Set c = ActiveChart
    Dim z As Integer, j As Integer
    Dim a As Variant
    Dim isEmptySeries As Boolean
   
    c.HasLegend = False
    c.HasLegend = True
   
    For z = 1 To c.SeriesCollection.count
        .SeriesCollection(z).ApplyDataLabels Type:=xlDataLabelIsShowValue, AutoText:=True, LegendKey:=False
        isEmptySeries = True
       
        For j = .SeriesCollection(z).Points.count To 1 Step -1
            If c.SeriesCollection(z).Points(j).DataLabel.text = 0 Then
                c.SeriesCollection(z).Points(j).HasDataLabel = False
            Else
                isEmptySeries = False
            End If
        Next j
       
        If isEmptySeries Then
            c.SeriesCollection(z).Delete
        End If
    Next z
   
   
        ' CI ref name
        .Name = "=Sheet2!$E$41"
       
        'place data points to graph
        .XValues = rXVals
        .Values = rYVals.Columns(i)
       
        'Edit marker type for CI ref
        .MarkerSize = 5
        .MarkerStyle = xlMarkerStyleStar
       
    End With
    Next i
  
End With
  
 
  
End Sub
 
Upvote 0
Yea you've spliced it in there and that's why you're having issues...you're currently trying to delete empty series from within "NewSeries"...you need to be careful of putting things in between your With/End With's.

Are you typing this in notepad or something? I've noticed multiple times that things that would normally be auto-capitalized (such as the "R" in "range", or the "C" in "chart" & "count") are not...which implies you aren't copying directly from VBA.

I've reformatted it again for you and attempted to put everything where it should be:

Code:
Private Sub CommandButton3_Click()
Dim rXVals As Range
Dim rYVals As Range
Dim i As Long
Dim LastRow As Long
Dim Name As Range

' If no data is found show error message box
With Worksheets("Sheet2")
    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    
    If LastRow < 41 Then
        MsgBox "No data found.", vbInformation
        Exit Sub
    End If
    
    'select columns C,I and J
    Set rXVals = .Range("C41:C" & LastRow)
    Set rYVals = .Range("I41:J" & LastRow)
End With

' place chart in location, choose chart type and plot data series
With ActiveSheet.ChartObjects.Add(Left:=30, Width:=650, Top:=20, Height:=375).Chart
    .ChartType = xlXYScatter
    
    For i = 1 To rYVals.Columns.Count
        With .SeriesCollection.NewSeries
            ' CI ref name
            .Name = "=Sheet2!$E$41"
            
            'place data points to graph
            .XValues = rXVals
            .Values = rYVals.Columns(i)
            
            'Edit marker type for CI ref
            .MarkerSize = 5
            .MarkerStyle = xlMarkerStyleStar
        End With
    Next i
End With

ActiveSheet.ChartObjects(1).Activate

'Delete empty series
Dim c As Chart: Set c = ActiveChart
Dim z As Integer, j As Integer
Dim a As Variant
Dim isEmptySeries As Boolean

c.HasLegend = False
c.HasLegend = True

For z = 1 To c.SeriesCollection.Count
    c.SeriesCollection(z).ApplyDataLabels Type:=xlDataLabelIsShowValue, AutoText:=True, LegendKey:=False
    
    isEmptySeries = True

    For j = .SeriesCollection(z).Points.Count To 1 Step -1
        If c.SeriesCollection(z).Points(j).DataLabel.Text = 0 Then
            c.SeriesCollection(z).Points(j).HasDataLabel = False
        Else
            isEmptySeries = False
        End If
    Next j
    
    If isEmptySeries Then
        c.SeriesCollection(z).Delete
    End If
Next z
End Sub

Also, just to avoid confusion, I would highly suggest doing this in two steps/two separate macros:
1. Macro 1 - Create the chart. Once you get everything exactly as you want it as far as formatting and all of that....then:
2. Macro 2 - Delete empty series.

So the macro that runs when you click the button:

Code:
Sub CreateChart()
**All of the code to create the chart**

Call DeleteEmptySeriesMacro 'Call the second macro to delete the empty series.
End Sub

That way everything is segregated and you stop mixing everything together.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top