cellMatrix.net

Create X Axis Labels from Array

Recently I was working on a chart that required text entries in the Category (X) axis label input as opposed to a worksheet range as the source. In other words, in the chart source data dialog box the Category (X) axis label input normally looks like this:

but I needed it to look like this:

Rather than inputting all of the the data by hand I had hoped that the process could be automated. My thought was to use a macro to load the data from a worksheet range into an array and then load the array as Category (X) axis values. Below is that macro:

Sub Create_Axis_Values_From_Array()
    Dim X As Long
    Dim R As Range
    Dim N As String
    Set R = ActiveSheet.Range("B2:B13")
    N = "={"
    For X = 1 To R.Count
        N = N & """" & R(X).Value & ""","
    Next
    N = Left(N, Len(N) - 1) & "}"
    ActiveChart.SeriesCollection(1).XValues = N
End Sub

The same macro works to create a named range by changing the second-to-last statement in the procedure.

Sub Create_Named_Range_From_Array()
    Dim X As Long
    Dim R As Range
    Dim N As String
    Set R = ActiveSheet.Range("B2:B13")
    N = "={"
    For X = 1 To R.Count
        N = N & """" & R(X).Value & ""","
    Next
    N = Left(N, Len(N) - 1) & "}"
    Names.Add Name:="Months", RefersTo:=N
End Sub

A special thanks to Rick Rothstein - Excel MVP - who helped me set up the array.