Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: How to delete the empty series on a legend?

  1. #1
    Board Regular
    Join Date
    May 2015
    Posts
    185
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to delete the empty series on a legend?

    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 Code:
    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 Code:
    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.

  2. #2
    Board Regular
    Join Date
    Nov 2015
    Posts
    496
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to delete the empty series on a legend?

    if you step through the code (F8), what is the value of a? Check the locals window (view > locals window).

  3. #3
    Board Regular
    Join Date
    May 2015
    Posts
    185
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to delete the empty series on a legend?

    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!! :-/

  4. #4
    Board Regular
    Join Date
    Nov 2015
    Posts
    496
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to delete the empty series on a legend?

    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.

  5. #5
    Board Regular
    Join Date
    May 2015
    Posts
    185
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to delete the empty series on a legend?

    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.....

  6. #6
    Board Regular
    Join Date
    Nov 2015
    Posts
    496
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to delete the empty series on a legend?

    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 by DushiPunda; Jun 19th, 2017 at 05:33 AM.

  7. #7
    Board Regular
    Join Date
    May 2015
    Posts
    185
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to delete the empty series on a legend?

    Quote Originally Posted by DushiPunda View Post
    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..

  8. #8
    Board Regular
    Join Date
    Nov 2015
    Posts
    496
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to delete the empty series on a legend?

    Quote Originally Posted by dboone25 View Post
    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.

  9. #9
    Board Regular
    Join Date
    May 2015
    Posts
    185
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to delete the empty series on a legend?

    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.

  10. #10
    Board Regular
    Join Date
    Nov 2015
    Posts
    496
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to delete the empty series on a legend?

    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 by DushiPunda; Jun 19th, 2017 at 11:15 PM.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com