VBA macro (Excel) to create pie chart with legends that exclude 0% values

Hi!
In a worksheet I've got a simple 2-columns table (name + value); the values  can occasionally drop to 0.0%. I'm looking for a macro (VBA) which can reproduce a pie chart on the same sheet which does not show the 0.0% figures - neither the value, nor the label (name).
The below macro does indeed erase the 0%-labels. However, instead of displaying for each label (name) the appendant value as listed in the table, the macro produces percentages referring to the total of this pie charts' values.  

I tinkered with the command
        Type:=xlShowLabelAndPercent
(e.g. starting with "Type:= xlDataLabelsShowValue" and take it from there, but got nowhere...).

Any help would be highly appreciated, thank you.
Karl




Sub ClearLabels()

       Worksheets("abc").ChartObjects(1).Chart.ApplyDataLabels _
        Type:=xlShowLabelAndPercent
   
      For Each x In Worksheets("abc").ChartObjects(1). _
         Chart.SeriesCollection(1).Points

            If InStr(x.DataLabel.Text, Chr(10) & "0.0") > 0 Then
            x.DataLabel.Delete
         End If
      Next
   End Sub
CandidKarlitoAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
pls try

 Sub ClearLabels()

        Worksheets("abc").ChartObjects(1).Chart.ApplyDataLabels xlDataLabelsShowValue, , , , , True
   
       For Each x In Worksheets("abc").ChartObjects(1). _
          Chart.SeriesCollection(1).Points

             If InStr(x.DataLabel.Text, " 0.0") > 0 Then
             x.DataLabel.Delete
          End If
       Next
    End Sub

Regards
0
 
Rgonzo1971Commented:
Hi,

pls try

    Worksheets("abc").ChartObjects(1).Chart.ApplyDataLabels xlDataLabelsShowValue, , , , , True

Regards
0
 
CandidKarlitoAuthor Commented:
Hello 71!
Tks for your line - and sorry for my delayed reply.

Please have a look at the attached Xls. I have added a mini pie chart to illustrate what I need ("intended result").
I added your command into the macro but this didn't work. Most likely my own fault.

Your advice will be highly appreciated.
Best Karl
TEST---macro-pie-chart-legends.xls
0
 
CandidKarlitoAuthor Commented:
Thank you very much, Rgonzo1971, for your fast  response - and your solution!
This macro of yours is short and handy, works  fine in all my large excel pie charts!

Best regards
Karl
0
All Courses

From novice to tech pro — start learning today.