Turning OFF the visibility of a horizontal (category) axis labels if certain conditions apply

[Piaba]

I'm having trouble with my chart creation macro and would greatly appreciate any help.

I want it to import data from the sourcesheet and create a chart for each row (macro is working for it); however, I'm trying to make my macro to edit the charts and not include a specific year if it has "Output V", "Output A" or "Anode bed Resistance" equal to "0" or is empty.

Ex: in 2017 there was no data reported for Output V, so I want my chart to not display 2017 for any of the data series, but display only from 2018 to 2022.

Can someone please guide me on how to make it work?

Chart creation code below:

Sub CreateChartsForAllRows()
    Dim i As Integer
    Dim lastRow As Long
    Dim chartSheet As Worksheet
    Dim chartRange As Range
    Dim chartObject As chartObject
    Dim leftPos As Double
    Dim topPos As Double
    Dim chartName As String
    Dim co As chartObject
    
    ' Set the chart sheet
    Set chartSheet = Sheets("Template - Charts")
    
    ' Delete existing charts on the "Template - Charts" sheet
    For Each co In chartSheet.ChartObjects
        co.Delete
    Next co
    
    ' Find the last used row in column A of "Template - Raw Data" sheet
    lastRow = Sheets("Template - Raw Data").Cells(Rows.Count, "A").End(xlUp).row
    
    ' Loop through each row starting from row 3 to the last used row
    For i = 3 To lastRow
        ' Set the chart range based on the row number
        Set chartRange = Sheets("Template - Raw Data").Range("AI" & i & ":AO" & i)
        
        ' Add a new chart object to the "Template - Charts" sheet
        Set chartObject = chartSheet.ChartObjects.Add(Left:=100, Width:=500, Top:=75, Height:=300) ' Adjust dimensions here
        
        ' Set chart data
        With chartObject.chart
            .chartType = xlLineMarkers
            .SetSourceData Source:=chartRange
            .HasTitle = True
            
            ' Get the chart name from columns A, B, and C of "Template - Raw Data" tab
            chartName = Sheets("Template - Raw Data").Range("A" & i).value & " - " & _
                        Sheets("Template - Raw Data").Range("B" & i).value & " - " & _
                        Sheets("Template - Raw Data").Range("C" & i).value
            
            ' Set the chart title
            .ChartTitle.Text = chartName
            
            ' Set axis titles
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Year"
            
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Text = "V & A"
            
            ' Add series data based on your original code
            For Each series In .SeriesCollection
                series.xValues = Array(2017, 2018, 2019, 2020, 2021, 2022)
            Next series
            
            'Rated V series
            .SeriesCollection.newSeries
            .SeriesCollection(1).Name = "=""Rated V"""
            .SeriesCollection(1).Values = "='Template - Raw Data'!$AL$" & i & ":$AQ$" & i
            .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
            .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            .SeriesCollection(1).MarkerForegroundColor = RGB(255, 0, 0)
            .SeriesCollection(1).MarkerBackgroundColor = RGB(255, 0, 0)

            'Output V series
            .SeriesCollection.newSeries
            .SeriesCollection(2).Name = "=""Output V"""
            .SeriesCollection(2).Values = "='Template - Raw Data'!$AG$" & i & ",'Template - Raw Data'!$AC$" & i & _
                ",'Template - Raw Data'!$Y$" & i & ",'Template - Raw Data'!$U$" & i & _
                ",'Template - Raw Data'!$Q$" & i & ",'Template - Raw Data'!$K$" & i
            .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(255, 102, 0)
            .SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(255, 102, 0)
            .SeriesCollection(2).MarkerForegroundColor = RGB(255, 102, 0)
            .SeriesCollection(2).MarkerBackgroundColor = RGB(255, 102, 0)
            
            'Rated A series
            .SeriesCollection.newSeries
            .SeriesCollection(3).Name = "=""Rated A"""
            .SeriesCollection(3).Values = "='Template - Raw Data'!$AS$" & i & ":$AY$" & i
            .SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(0, 0, 128)
            .SeriesCollection(3).Format.Fill.ForeColor.RGB = RGB(0, 0, 128)
            .SeriesCollection(3).MarkerForegroundColor = RGB(0, 0, 128)
            .SeriesCollection(3).MarkerBackgroundColor = RGB(0, 0, 128)

            'Output A series
            .SeriesCollection.newSeries
            .SeriesCollection(4).Name = "=""Output A"""
            .SeriesCollection(4).Values = "='Template - Raw Data'!$AH$" & i & ",'Template - Raw Data'!$AD$" & i & _
                ",'Template - Raw Data'!$Z$" & i & ",'Template - Raw Data'!$V$" & i & _
                ",'Template - Raw Data'!$R$" & i & ",'Template - Raw Data'!$M$" & i
            .SeriesCollection(4).Format.Line.ForeColor.RGB = RGB(100, 100, 255)
            .SeriesCollection(4).Format.Fill.ForeColor.RGB = RGB(100, 100, 255)
            .SeriesCollection(4).MarkerForegroundColor = RGB(100, 100, 255)
            .SeriesCollection(4).MarkerBackgroundColor = RGB(100, 100, 255)

            'Anode bed resistance series
            .SeriesCollection.newSeries
            .SeriesCollection(5).Name = "=""Anode Bed Resistance"""
            .SeriesCollection(5).Values = "='Template - Raw Data'!$AJ$" & i & ",'Template - Raw Data'!$AF$" & i & _
                ",'Template - Raw Data'!$AB$" & i & ",'Template - Raw Data'!$X$" & i & _
                ",'Template - Raw Data'!$T$" & i & ",'Template - Raw Data'!$P$" & i
            .SeriesCollection(5).Format.Line.ForeColor.RGB = RGB(0, 0, 0)
            .SeriesCollection(5).Format.Fill.ForeColor.RGB = RGB(0, 0, 0)
            .SeriesCollection(5).MarkerForegroundColor = RGB(0, 0, 0)
            .SeriesCollection(5).MarkerBackgroundColor = RGB(0, 0, 0)
            
            ' Remove "Series 6" if it exists
            On Error Resume Next
            .SeriesCollection(6).Delete
            On Error GoTo 0

            ' Move "Anode Bed Resistance" series to the secondary Y-axis
            .SeriesCollection(5).AxisGroup = 2
            
            ' Set the title for the secondary Y-axis (workaround)
            .Axes(xlValue, xlSecondary).HasTitle = True
            .Axes(xlValue, xlSecondary).AxisTitle.Text = "Ohm"
        End With
        
        ' Assign the chart name
        chartObject.Name = chartName
        
        ' Calculate the position of the chart in the grid (3 charts per row)
        leftPos = ((i - 3) Mod 3) * 500 ' Adjust the multiplier for the width
        topPos = Int((i - 3) / 3) * 300
        
        ' Adjust the position of the chart
        chartObject.Left = leftPos + 100 ' Add 100 to start at B2
        chartObject.Top = topPos + 75    ' Add 75 to start at B2
        
    Next i
    
    MsgBox "Charts created successfully!", vbInformation
    
End Sub

 

[NdNoviceHlp]

Trial changing your 0 and empty values to #N/A and the chart will ignore those values. HTH. Dave