LegendEntries, Range, PieChart, Delete, Condition

Question

I've been trying to find the mistake or what I'm doing wrong for a few days. I show here the current output and what it should be. I think maybe I'm defining the range with some mistake, or maybe LegendEntries(i), doesn't work with the same index as Points(i). Any suggestion ? Or maybe how could I do this from another approach ?

The thing here is to get a chart and delete the legend and labels associated with "0" values.

The First graphic is my current output. The second one is my desire output, and what I expected to get with my code.

The Legends that have 0 values are "Asia" and "Latam", but my current output is throwing "Latam" and it should throw "RoW", because "RoW" insn't associated with 0 values, its value is "5.0%".

Sub CreateChart()
On Error Resume Next
Sheets(1).ChartObjects.Delete

    Dim MyChart As Chart
    Dim rowi As Integer
    Dim MyRange As Range


rowi = Range("I7").Row ' Index to select last row non empty, in this example it's row number 11.
Do While Sheets(1).Cells(rowi, Range("I7").Column).Value = _
IsEmpty(Cells(rowi, Range("I7").Column))
rowi = rowi + 1
Loop

'----------------------------------------------------CHART---------------------------------------------------------------

Set MyRange = Range("I6:M6" & ",I" & rowi & ":M" & rowi)

Set MyChart = Sheets(1).Shapes.AddChart(xlPie).Chart

     MyChart.SetSourceData Source:=MyRange

With MyChart.SeriesCollection(1)
 .HasDataLabels = True
 .DataLabels.NumberFormat = "0.0%"
End With

With MyChart
 .HasLegend = True
End With

For i = 1 To (Range("M6").Column - Range("I6").Column + 1)

If  Cells(rowi, Range("I6").Column + i - 1).Value = 0 Then
MyChart.SeriesCollection(1).Points(i).DataLabel.Delete
MyChart.Legend.LegendEntries(i).Delete
End If

Next i
End Sub

Answer 1

Tomás, Here you go. The problem you were having is that each time you deleted one of the legend entries from the LegendEntries object, the index numbers of the remaining entries all shift down 1 to fill the hole. So after you deleted index 3 (Asia), index 4 became 3, 5 became 4, and so on... To solve the problem I just stepped backwards through the index numbers, thus ensuring that the deletions would not effect the order of things to come.

 Sub CreateChart()
    On Error Resume Next
    Sheets(1).ChartObjects.Delete
        Dim MyChart As Chart
        Dim rowi As Integer
        Dim MyRange As Range
    rowi = Range("I7").Row ' Index to select last row non empty, in this example it's row number 11.
    Do While Sheets(1).Cells(rowi, Range("I7").Column).Value = _
        IsEmpty(Cells(rowi, Range("I7").Column))
        rowi = rowi + 1
    Loop

    '----------------------------------------------------CHART---------------------------------------------------------------

    Set MyRange = Range("I6:M6" & ",I" & rowi & ":M" & rowi)

    Set MyChart = Sheets(1).Shapes.AddChart(xlPie).Chart
         MyChart.SetSourceData Source:=MyRange

    With MyChart.SeriesCollection(1)
     .HasDataLabels = True
     .DataLabels.NumberFormat = "0.0%"
    End With
    MyChart.HasLegend = True

    For i = (Range("M6").Column - Range("I6").Column + 1) To 1 Step -1 ' <---CHANGED THIS
        If Cells(rowi, Range("I6").Column + i - 1).Value = 0 Then
            MyChart.SeriesCollection(1).Points(i).DataLabel.Delete
            MyChart.Legend.LegendEntries(i).Delete
        End If
    Next i
End Sub

Hope this helps, should you have any questions, please don't hesitate to ask.