Remove just 0 values from Chart Excel

Question

I have been trying to remove these 0 from the Excel Chart i have watched multiple videos used Format Control #,##0;-#,##0;;. and different things available on Google but nothing is happening. I have replace the 0 value with Nothing#.

Go to advance uncheck the Show a zero in cells that have zero values then go to Select Data Source => Hidden and Empty Cells but still nothing happens.

Still issue is not resolved can someone please help me with this problem i will really appreciate the help.

If it can be resolve through VBA then please share the code or Manual Solution.

I have tried to write record the code but i really do not know how to do this.

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.ChartObjects("Chart 16").Activate
    ActiveChart.FullSeriesCollection(3).Select
    ActiveChart.SetElement (msoElementDataLabelCenter)
    ActiveChart.FullSeriesCollection(3).DataLabels.Select
    ActiveChart.FullSeriesCollection(3).Points(1).DataLabel.Select
    Selection.Delete
    ActiveSheet.ChartObjects("Chart 16").Activate
    ActiveChart.FullSeriesCollection(3).DataLabels.Select
    ActiveChart.FullSeriesCollection(3).Points(9).DataLabel.Select
    Selection.Delete
    ActiveSheet.ChartObjects("Chart 16").Activate
    ActiveChart.FullSeriesCollection(1).Select
    ActiveChart.SetElement (msoElementDataLabelCenter)
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.FullSeriesCollection(1).Points(9).DataLabel.Select
    Selection.Delete
    ActiveSheet.ChartObjects("Chart 16").Activate
    ActiveChart.FullSeriesCollection(2).Select
    ActiveChart.SetElement (msoElementDataLabelCenter)
    ActiveChart.FullSeriesCollection(2).DataLabels.Select
    ActiveChart.PlotArea.Select
    ActiveChart.FullSeriesCollection(2).DataLabels.Select
    ActiveChart.FullSeriesCollection(2).Points(9).DataLabel.Select
    Selection.Delete
    ActiveSheet.ChartObjects("Chart 16").Activate
    ActiveChart.FullSeriesCollection(2).DataLabels.Select
    ActiveChart.FullSeriesCollection(2).Points(8).DataLabel.Select
    Selection.Delete
    ActiveSheet.ChartObjects("Chart 16").Activate
    ActiveChart.FullSeriesCollection(2).DataLabels.Select
    ActiveChart.FullSeriesCollection(2).Points(6).DataLabel.Select
    Selection.Delete
    ActiveSheet.ChartObjects("Chart 16").Activate
    ActiveChart.FullSeriesCollection(2).DataLabels.Select
    ActiveChart.FullSeriesCollection(2).Points(5).DataLabel.Select
    Selection.Delete
    ActiveSheet.ChartObjects("Chart 16").Activate
    ActiveChart.FullSeriesCollection(3).DataLabels.Select
    ActiveChart.ChartArea.Select
End Sub

I have tried with this one but it removes all the labels rather than 0.

Sub chartth()

Sheet5.ChartObjects("Chart 16").Activate

With ActiveChart.SeriesCollection(1)
    For i = 1 To .Points.Count
        If .Points(i).HasDataLabel = False Then
            .Points(i).Select
            ActiveChart.SetElement (msoElementDataLabelShow)
                If .Points(i).DataLabel.Text = 0 Then
                    .Points(i).HasDataLabel = False
                    .Points(i).DataLabel.ShowValue = False
                End If
        ElseIf .Points(i).DataLabel.Text = 0 Then
            .Points(i).HasDataLabel = False
            .Points(i).DataLabel.ShowValue = False
        End If
    Next i
End With

With ActiveChart.SeriesCollection(2)
    For i = 1 To .Points.Count
        If .Points(i).HasDataLabel = False Then
            .Points(i).Select
            ActiveChart.SetElement (msoElementDataLabelShow)
                If .Points(i).DataLabel.Text = 0 Then
                    .Points(i).HasDataLabel = False
                    .Points(i).DataLabel.ShowValue = False
                End If
        ElseIf .Points(i).DataLabel.Text = 0 Then
            .Points(i).HasDataLabel = False
            .Points(i).DataLabel.ShowValue = False
        End If
    Next i
End With


With ActiveChart.SeriesCollection(3)
    For i = 1 To .Points.Count
        If .Points(i).HasDataLabel = False Then
            .Points(i).Select
            ActiveChart.SetElement (msoElementDataLabelShow)
                If .Points(i).DataLabel.Text = 0 Then
                    .Points(i).HasDataLabel = False
                    .Points(i).DataLabel.ShowValue = False
                End If
        ElseIf .Points(i).DataLabel.Text = 0 Then
            .Points(i).HasDataLabel = False
            .Points(i).DataLabel.ShowValue = False
        End If
    Next i
End With

This is connected to multiple years so when i changed the year data is loaded accordingly. If i remove the 0 then which data have values greater than 0 will not show as well — , Feb 16 '21 at 17:58
The second snippet looks pretty close to what the answer should be... may be able to take a closer look. — BigBen, Feb 16 '21 at 18:30

BigBen · Accepted Answer · 2022-02-24T17:12:23.650

Perhaps try something like the following, which applies data labels to each Series, then loops over the Points in each Series and removes the DataLabel if it's 0.

Sub ApplyLabelsAndClearZeros(ByVal chrt As Chart)
    Dim ser As Series
    For Each ser In chrt.SeriesCollection
        ser.ApplyDataLabels
        
        Dim pnt As Point
        For Each pnt In ser.Points       
            If pnt.DataLabel.Text = "0" Then
                pnt.HasDataLabel = False
            End If          
        Next
    Next
End Sub

Call it like the following:

Sub Test()
    ApplyLabelsAndClearZeros Sheet1.ChartObjects(1).Chart
End Sub

Remove just 0 values from Chart Excel

1 Answers1