Search code examples
excelvbaexcel-charts

Remove just 0 values from Chart Excel

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.

enter image description here

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
Solution

  • 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