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

Question

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

I would like to have some guidance on how to do it.

Answer 1

This is refactored quite a bit from your original code, so I hope it makes sense. I'm not able to test, since I don't have your data, but it at least compiled
See comments in code.

Option Explicit

Sub CreateChartsForAllRows()
    Const CHARTS_PER_ROW As Long = 3
    Const CHT_WIDTH As Long = 500
    Const CHT_HEIGHT As Long = 300
    Dim i As Long
    Dim lastRow As Long
    Dim chartSheet As Worksheet, wsData As Worksheet, wb As Workbook
    Dim chartRange As Range
    Dim chartObject As chartObject, cht As chart, yvals
    Dim leftPos As Double
    Dim topPos As Double
    Dim chartName As String
    Dim co As chartObject, ser As Series
    
    ' Set the chart sheet
    Set wb = ThisWorkbook 'for example
    Set chartSheet = wb.Worksheets("Template - Charts")
    Set wsData = wb.Worksheets("Template - Raw Data")
    
    ' 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 = wsData.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 = wsData.Range("AI" & i & ":AO" & i)
        
        ' Add a new chart object to the charts sheet
        Set chartObject = chartSheet.ChartObjects.Add( _
              Left:=100, Width:=CHT_WIDTH, Top:=75, Height:=CHT_HEIGHT)
        Set cht = chartObject.chart
        
        ' Set chart data
        With cht
            .ChartType = xlLineMarkers
            .SetSourceData Source:=chartRange
            '##clear any "auto-plotted" series
            Do While .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            Loop
            
            ' Set the chart title from columns A, B, and C
            chartName = wsData.Range("A" & i).Value & " - " & _
                        wsData.Range("B" & i).Value & " - " & _
                        wsData.Range("C" & i).Value
            .HasTitle = True
            .ChartTitle.text = chartName
            
            ' Set axis titles
            SetAxisTitle .Axes(xlCategory, xlPrimary), "Year"
            SetAxisTitle .Axes(xlValue, xlPrimary), "V & A"
            
            '### start adding series ###
            
            'Rated V series
            yvals = ToArray(wsData.Range("AL1:AQ1").Offset(i - 1))
            AddChartSeries cht, "Rated V", GetYears(yvals), _
                           yvals, RGB(255, 0, 0) 'vbRed
            
            'Output V series
            yvals = ToArray(wsData.Range("AG1,AC1,Y1,U1,Q1,K1").Offset(i - 1))
            AddChartSeries cht, "Output V", GetYears(yvals), _
                    yvals, RGB(255, 102, 0)
           
            'Rated A series
            yvals = ToArray(wsData.Range("AS1:$AY1").Offset(i - 1))
            AddChartSeries cht, "Rated A", GetYears(yvals), yvals, RGB(0, 0, 128)
            

            'Output A series
            yvals = ToArray(wsData.Range("AH1,AD1,Z1,V1,R1,M1").Offset(i - 1))
            AddChartSeries cht, "Output A", GetYears(yvals), yvals, RGB(100, 100, 255)
            

            'Anode bed resistance series
            yvals = ToArray(wsData.Range("AJ1,AF1,AB1,X1,T1,P1").Offset(i - 1))
            Set ser = AddChartSeries(cht, "Anode Bed Resistance", _
                        GetYears(yvals), yvals, RGB(0, 0, 0))
                
            ser.AxisGroup = 2                                    'modify the returned series
            SetAxisTitle .Axes(xlValue, xlSecondary), "Ohm"
        End With
        
        chartObject.Name = chartName ' Assign the chart name
        leftPos = ((i - 3) Mod CHARTS_PER_ROW) * CHT_WIDTH
        chartObject.Left = leftPos + 100 ' Add 100 to start at B2
        
        topPos = Int((i - 3) / CHARTS_PER_ROW) * CHT_HEIGHT
        chartObject.Top = topPos + 75    ' Add 75 to start at B2
        
    Next i
    
    MsgBox "Charts created successfully!", vbInformation
    
End Sub

'Set the title for a chart axis
Sub SetAxisTitle(ax As Axis, txt As String)
    ax.HasTitle = True
    ax.AxisTitle.text = txt
End Sub

'from the size of the y_values array, return the right-size X values array
Function GetYears(yvals)
    Select Case UBound(yvals)
        Case 5: GetYears = Array(2017, 2018, 2019, 2020, 2021, 2022)
        Case 4: GetYears = Array(2018, 2019, 2020, 2021, 2022)
        Case Else: MsgBox "Unexpected Y-value array size!", vbExclamation 'should not get here!
    End Select
End Function


'Return an array of values from a range
'Optionally, do not collect the first value if the cell is empty
Function ToArray(rng As Range, Optional skipFirstIfEmpty = False) As Variant
    Dim rv, c As Range, i, n, skip As Boolean
    
    skip = Len(rng(1).Value) = 0 And skipFirstIfEmpty
    n = IIf(skip, rng.Cells.Count - 2, rng.Cells.Count - 1)
    ReDim rv(0 To n)
    For Each c In rng.Cells
        If Not skip Then     'collect if not skipping first value
            rv(i) = c.Value
            i = i + 1
        End If
        skip = False         'reset flag after first point
    Next c
    ToArray = rv
End Function

'Adds a series to chart `cht` and sets data and color
'Returns the added series in case other customizations to the series are needed
Function AddChartSeries(cht As chart, serName As String, xvals, yvals, clr As Long) As Series
    Set AddChartSeries = cht.SeriesCollection.NewSeries 'returns the new series once configured
    With AddChartSeries
        .Name = serName
        .XValues = xvals
        .Values = yvals
        .Format.Line.ForeColor.RGB = RGB(0, 0, 0)
        .Format.Fill.ForeColor.RGB = RGB(0, 0, 0)
        .MarkerForegroundColor = RGB(0, 0, 0)
        .MarkerBackgroundColor = RGB(0, 0, 0)
    End With
End Function