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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Rgonzo1971Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.