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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
if you step through the code (F8), what is the value of a? Check the locals window (view > locals window).
 
Upvote 0
Not sure what I have done now but the next error is

'Run-time error '91':

Object variable or With block variable not set..


Debug showing on line
Code:
c.HasLegend = False

What have I done!! :-/
 
Upvote 0
Not sure if those two lines (..= False & ..= True) are necessary for the code to work, but for testing purposes, you can just comment them out. I guess I should expand on my original question and ask what the value and type is for 'a' when you are stepping through the code.
 
Upvote 0
Hi, I have commented out what you mentioned above but i still keep getting Run-time error '91': Object variable or With block variable not set

So I wont be able to debug to find the value of 'a'....not sure...even if i comment out .HasLehgend the code wont do anything.....
 
Upvote 0
Since you're using ActiveChart...do you have the chart open prior to trying to step through the code? If you don't have any chart open, then I guess in theory there isn't an "ActiveChart" and that could be causing your errors. Just speculating though...I haven't done any work with charts.

Edit: Regarding above, I'm only talking about your "Run-time error '91': Object variable or With block variable not set" errors.
 
Last edited:
Upvote 0
Since you're using ActiveChart...do you have the chart open prior to trying to step through the code? If you don't have any chart open, then I guess in theory there isn't an "ActiveChart" and that could be causing your errors. Just speculating though...I haven't done any work with charts.

Edit: Regarding above, I'm only talking about your "Run-time error '91': Object variable or With block variable not set" errors.


No I dont have the chart open as Ihave it set to a button in where it auto selects the columns, plots the data ..etc...

Really stuck on this one..
 
Upvote 0
No I dont have the chart open as Ihave it set to a button in where it auto selects the columns, plots the data ..etc...

Really stuck on this one..

You need to have it open before the code will run. I just copied your code into a test macro, tried to run the code without a chart open and I got the same error with the same offending line of code. I then created an empty chart and I was able to step past those lines. Open/create your chart, then step through the code and tell me what the value and type of a is from the Locals window.
 
Upvote 0
Im still lost with this...


This is my code for the command button which selects the data in series, creates the graph with the selected data..


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
   
   
   
    'DELETE EMPTY SERIES
   
     Dim c As chart: Set c = chart
     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
   
        .Name = "=Sheet2!$E$41" ' optional
        .XValues = rXVals
        .Values = rYVals
        '.bYValues = bYVals
    End With
   
  
   
End With
   
  
End Sub



Hope this will help.
 
Upvote 0
So if you step through this code, it should create the chart for you, no? And then after it creates the chart, you go into the "delete empty series" section of your macro...as long as the chart is created...then you should be able to keep stepping through.

Also, you need to fix this line:

Dim c As chart: Set c = chart

Change it to:

Dim c As chart: Set c = ActiveChart
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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