Questions about hiding the zero value in the chart

Question

This time I write my macro. But it still did not work. I would like to design a macro that can detect any zero value(in y-axis) in the graph and then hide the correlated x-axis point. Here is the Macro.

Sub Delete0()

    ActiveSheet.ChartObjects("YYY").Activate
    For x = 1 To ActiveChart.SeriesCollection(1).Points.Count
        If ActiveChart.FullSeriesCollection(1).Points(x).DataLabels.Count = 0 Then
            ActiveChart.ChartGroups(1).FullCategoryCollection(x).IsFiltered = True
        End If
    Next x

End Sub

Answer 1

So if you hide rows in the source data where Y = 0 then these points will not be plotted on the chart.

This is easy if your data is set-up as an Excel table and you use the macro-recorder whilst performing the filter. This would give you starting code:

Then will a little research into:

Autofilter

and

ListObject

You could have tweaked the code and had something like:

Option Explicit

Sub HideCharts()

    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim targetTable As ListObject

    Set wb = ThisWorkbook
    Set wsData = wb.Worksheets("Sheet1")
    Set targetTable = wsData.ListObjects("Table1")

    'Check that there are other values apart from 0 so don't try to filter to nothing
    If Application.WorksheetFunction.CountIf(targetTable.DataBodyRange.Columns(2), ">" & 0) > 0 Then ' DataBodyRange.Columns(2) = y column

        With targetTable.Range

           .AutoFilter Field:=2 'remove filter
           .AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlFilterValues

        End With

    End If


End Sub

Version 2 Plotting chart series to ignore zeroes (needs some refining).

Existing chart and then adding series to it (X and Y) using data from sheet.

Load sheet data into array, loop and concatenate non zero values. Split these strings to create arrays using a function by @Aiken, which I have adapted, to ensure that the arrays plotted as series sources are integer, rather than text. Using Split$ returns strings which won't plot in the desired way. With more time I would further adapt this to use Long to avoid overflow. If of use, I will do this. For now, this illustrates the principle you are after, I believe.

Option Explicit

Public Sub AddSeriesWithoutZero()

    Dim myChart As Chart

    Set myChart = ActiveSheet.ChartObjects("Chart 1").Chart

    Dim sourceData()

    sourceData = ActiveSheet.Range("A2:B5").Value

    Dim currRow As Long
    Dim textStringY As String
    Dim textStringX As String

    For currRow = LBound(sourceData, 1) To UBound(sourceData, 1)

        If Not sourceData(currRow, 2) = 0 Then

            textStringY = textStringY & CStr(sourceData(currRow, 2)) & ";"
            textStringX = textStringX & CStr(sourceData(currRow, 1)) & ";"

        End If

    Next currRow

    Dim arrayY() As Integer
    arrayY = SplitIntegers(textStringY, ";")

    Dim arrayX() As Integer
    arrayX = SplitIntegers(textStringX, ";")


    With myChart.SeriesCollection.NewSeries
             .XValues = arrayX  'xaxis
             .Values = arrayY  'yaxis
    End With

End Sub

Public Function SplitIntegers(ByVal StringToSplit As String, ByVal Sep As String) As Variant

    Dim arrStrings() As String
    Dim arrIntegers() As Integer
    Dim i As Long

    On Error GoTo Err_SplitIntegers
    arrStrings = Split$(StringToSplit, Sep)
    ReDim arrIntegers(LBound(arrStrings) To UBound(arrStrings) - 1)

    For i = LBound(arrStrings) To UBound(arrStrings) - 1 
        arrIntegers(i) = CInt(arrStrings(i))
    Next i

    SplitIntegers = arrIntegers
    Exit Function

Err_SplitIntegers:
    Select Case Err.Number
        Case 13 'Type Mismatch Error: StringToSplit contains non-numeric substrings
            On Error GoTo 0
            Err.Raise 9114, "SplitIntegers", _
                      "SplitIntegers failed: substring '" & arrStrings(i) & "' of string '" & StringToSplit & "' is not numeric"
        Case Else 'Unhandled error, return to calling code
            Dim iErrNum As Integer, strErrDesc As String
            iErrNum = Err.Number
            strErrDesc = Err.Description
            On Error GoTo 0
            Err.Raise iErrNum, "SplitIntegers", strErrDesc
    End Select
End Function

Result: