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

Piaba

New Member
Joined
Jan 31, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Trial changing your 0 and empty values to #N/A and the chart will ignore those values. HTH. Dave
 
Upvote 0

Forum statistics

Threads
1,224,208
Messages
6,177,148
Members
452,762
Latest member
manuha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top