How to get only visible category labels of excel chart via vba code

Question

1) Please run the following code.

Public Sub Macro1()
    
    'Delete all charts
    For i = ActiveSheet.Shapes.Count To 1 Step -1
        If ActiveSheet.Shapes(i).Type = msoChart Then
            ActiveSheet.Shapes(i).Delete
        End If
    Next i
    
    'Add a chart.
    With ActiveSheet.ChartObjects.Add(Left:=10, Top:=10, Width:=500, Height:=300)
        .Chart.Axes(xlCategory).TickLabels.Font.Size = 40
    End With
    
    'Add a serie.
    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
        .ChartType = xlLine
        .XValues = Array(1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000, 11000, 12000, 13000, 14000, 15000, 16000, 17000, 18000, 19000, 20000)
        .Values = Array(150, 200, 800, 1200, 900, 600, 900, 850, 1500, 1600, 1900, 1700, 600, 500, 450, 300, 500, 750, 900, 850)
    End With
    
    'Print all XValues
    For Each i In ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues
        Debug.Print i
    Next
    
    'The following code is doing same task comparing to above code.
    'For Each i In ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory).CategoryNames
        'Debug.Print i
    'Next
    
End Sub

2) Check if you have the following chart in your excel sheet after running of the code above.

3) Here is my question:

How to get only visible category labels of excel chart via vba code?

Please note that visible category names are 1000, 4000, 7000, 10000, 13000, 16000, 19000 as you can see the chart above.

Answer 1

The visible labels on the Axis are the TickLabels. The spacing between them is specified under Axis.TickLabelSpacing (If you have forced this to be a specific value, your can reset it to automatic with Axis.TickLabelSpacingIsAuto)

So, if Axis.TickLabelSpacing=3, then you only want to output every third CategoryName: 1000, 2000, 3000, 4000, 5000, 6000, 7000, etc

You can either do this by having an incremental variable to tell you if you should keep or ignore it:

Dim ToShow AS Long: ToShow = 0

For Each j In Sheet1.ChartObjects(1).Chart.Axes(xlCategory).CategoryNames
    If ToShow =0 Then Debug.Print j
    ToShow = (ToShow+1) mod Sheet1.ChartObjects(1).Chart.Axes(xlCategory).TickLabelSpacing
Next

Or you can you use a Stepped loop:

Dim NameList() AS Variant, CurrentName As Long
NameList = Sheet1.ChartObjects(1).Chart.Axes(xlCategory).CategoryNames

For CurrentName = LBound(NameList) To UBound(NameList) Step Sheet1.ChartObjects(1).Chart.Axes(xlCategory).TickLabelSpacing
    Debug.Print NameList(CurrentName)
Next

Depending on how things are set up, you may need to include Axis.Crosses in your code as an offset