none
remove the unwanted legend on a chart in excel RRS feed

  • Question

  • Hi guys,

    I would like to make charts and show legend on its right. Yet, the data was about 50 to 100 legend, whereas I only need to show ten of them, such as PC1, PC2, PC3 ... PC 10.

    Is there a way to solve this.

    Thanks is advance

    Wednesday, March 13, 2013 7:28 AM

Answers

  • If I follow what you are trying to do you need to loop the Seriescollection rather than the LegendEntries as I mentioned, something like this -

    Sub test()
    Dim i As Long
    Dim cht As Chart
    Dim lgd As Legend
            Set cht = ActiveChart
         Set lgd = cht.Legend
         For i = cht.SeriesCollection.Count To 1 Step -1
                 Select Case cht.SeriesCollection(i).Name
                 Case "a", "c", "e"
                         ' do nothing
                 Case Else
                         lgd.LegendEntries(i).Delete
                 End Select
         Next
    End Sub

    As written the code would delete legend entries linked to series whose names are not "a", "c", or "e", albeit with the caveats I described before. For your needs an If... ElseIf construct might be more appropriate.

    Peter Thornton

    • Marked as answer by CRVR Thursday, March 14, 2013 1:19 AM
    Wednesday, March 13, 2013 12:09 PM
    Moderator

All replies

  • Could you be more specific?

    Do you want kill all legends from your 100 charts automatically?

    You can do it one by one using button in ribbon or use VBA code:

    Sub Kill_Charts_Legend()
    'MVP OShon from VBATools
    Dim wks As Worksheet, wyk As Object
    For Each wks In ThisWorkbook.Worksheets
        For Each wyk In wks.ChartObjects
         wyk.Activate: ActiveChart.SetElement (msoElementLegendNone)
        Next wyk
    Next wks
    End Sub


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, March 13, 2013 9:36 AM
    Answerer
  • It can be tricky dealing with LegendEntries as there's no direct way of correlating the legend entry with the series, that said there are indirect ways.

    However if you have a straightforward chart, with each series the same chart type (or specifically of the same chartgroup), that's just been created (ie user hasn't deleted any entries in the interim), no trend lines etc, something like the following might suffice

    Sub test()
    Dim i As Long
    Dim cht As Chart
    Dim lgd As Legend
            Set cht = ActiveChart
         If cht Is Nothing Then
                 Exit Sub
         End If
         Set lgd = cht.Legend
            For i = lgd.LegendEntries.Count To 11 Step -1
                 lgd.LegendEntries(i).Delete
         Next
    End Sub

    As written this deletes all but the first 10 legend entries.

    If you want only 10 entries but not necessarily the first 10, loop the seriescollection in reverse order inspecting each series (its name perhaps) for some criteria that determines if you want to keep or delete its legend entry.

    Peter Thornton

    Wednesday, March 13, 2013 10:30 AM
    Moderator
  • Thank you guys.

    I tried Peter Thornton's code and it worked well until certain range of serries collection such as the first five or ten. I also tried to loop as I also have series name out of the first ten. For instance, the series name number 20-22 and 30-31 and so on.

    I would like to modify it into 'conditional' code by stating their range of number or directly to their series name such as follows.

    Sub test()
    Dim i As Long
    Dim cht As Chart
    Dim lgd As Legend
            Set cht = ActiveChart
         If cht Is Nothing Then
                 Exit Sub
         End If
         Set lgd = cht.Legend
            For i = lgd.LegendEntries.Count To 1 Step -1
                 If .Name <> "PC1" And .Name <> "PC2" And .Name <> "PC3" And .Name <> "PC22" Then
                 lgd.LegendEntries(i).Delete
                 End If
         Next
    End Sub

    Yet, this one does not work.

    I am sorry for the rough description. Thanks again for your help.

    Wednesday, March 13, 2013 11:33 AM
  • If I follow what you are trying to do you need to loop the Seriescollection rather than the LegendEntries as I mentioned, something like this -

    Sub test()
    Dim i As Long
    Dim cht As Chart
    Dim lgd As Legend
            Set cht = ActiveChart
         Set lgd = cht.Legend
         For i = cht.SeriesCollection.Count To 1 Step -1
                 Select Case cht.SeriesCollection(i).Name
                 Case "a", "c", "e"
                         ' do nothing
                 Case Else
                         lgd.LegendEntries(i).Delete
                 End Select
         Next
    End Sub

    As written the code would delete legend entries linked to series whose names are not "a", "c", or "e", albeit with the caveats I described before. For your needs an If... ElseIf construct might be more appropriate.

    Peter Thornton

    • Marked as answer by CRVR Thursday, March 14, 2013 1:19 AM
    Wednesday, March 13, 2013 12:09 PM
    Moderator
  • Thank you Peter.

    I applied the code for ex. "a", "b" and "f", but it worked with the following conditions.

    I got totally different result on the first time running the code, say j, k , and p. So I exit the workbook without saving it.

    Secondly I put the results on the code "j", "k", "p" and run it again; there I got a, b and f as I wanted.

    As you said, it's tricky, really, but it worked. I hope you know why this is happen. Or maybe you have another solution.

    Many thanks for your time Peter!



    • Edited by CRVR Wednesday, March 13, 2013 3:05 PM
    Wednesday, March 13, 2013 2:07 PM
  • Don't attempt to run the original code multiple times on the same chart!

    Consider a new chart with 10 series and 10 legend entries, each with indexes 1 to 10. When created series(5) relates to lgendentry(5) but subject the original caveats I mentioned. Lets say you run the code and remove every alternate legendentry, you'll end up with still 10 series but only 5 legendentries. The point being the respective series & entry indexes no longer correlate.

    Now it gets tricky if you add a bunch of new series and want to remove some of the new entries. Maybe you could adapt something like this but only if you know very well exactly what's going on with your chart

    dim cntDif as Long
    
    cntDif = cht.SeriesCollection(i) -    lgd.LegendEntries.count
             For i = cht.SeriesCollection.Count To 1 Step -1
                            Select Case cht.SeriesCollection(i).Name
                            Case "a", "c", "e"
                                            ' do nothing
                            Case Else
                                            lgd.LegendEntries(i - cntDiff).Delete
                            End Select
            Next

    I haven't actually tested that but it hopefully gives the approach for adapting. If the user has changed things you might be unaware of it gets even more difficult.

    Peter Thornton

    Wednesday, March 13, 2013 2:51 PM
    Moderator
  • Thank you Oskar Shon. I think I made mistake to mention that there was 50-100 legends. It should be legend entries or series instead.

    But it gives another solution for I have many charts to go.

    Thursday, March 14, 2013 1:15 AM
  • Thank you Peter. I corrected my former reply that I exit the workbook without saving it before running the code again (2nd time).

    At least your second code has provided the solution. I hope to find out soon.


    • Edited by CRVR Thursday, March 14, 2013 1:21 AM
    Thursday, March 14, 2013 1:21 AM