(Solved) – VBA x-axis dynamic range


I’m trying to set the max x value on an XYScatter chart to the series-defined name.

I generated a chart then added data to cells A7, B7, and C7 (shown below).

The max x value jumped from 5 to 8 and the major unit changed from 1 to 2. I want the max x value to be equal to the max value in column A and for the x axis to automatically update as data are entered (e.g. if I continued to add data up until cells A11, B11, and C11, the x axis would show 10).

I’d also like the major scale on the x axis to stay at 1 until 12 is on the x-axis).

ch.Chart.Axes(xlCategory).MaximumScale = WorksheetFunction.Max(Columns(1)) sets the max when the chart is generated but does not update when the max value is changed.

enter image description here

Any thoughts on how to accomplish this?

wsData.Names.Add Name:="Session", RefersToR1C1:="=OFFSET(Sheet1!R2C1,0,0,COUNTA(Sheet1!C1)-1)"
wsData.Names.Add Name:="Measure1", RefersToR1C1:="=OFFSET(Sheet1!R2C2,0,0,COUNTA(Sheet1!C2)-1)"
wsData.Names.Add Name:="Measure2", RefersToR1C1:="=OFFSET(Sheet1!R2C3,0,0,COUNTA(Sheet1!C3)-1)"

'Rename legend entries to match series entries'
i = 0
For Each cell In rng.Cells
    i = i   1
    ch.Chart.FullSeriesCollection(i).Name = cell.Text
Next cell

With ch.Chart
    .SeriesCollection(1).XValues = "Sheet1!Session"
    .SeriesCollection(1).Values = "Sheet1!Measure1"
    .SeriesCollection(2).XValues = "Sheet1!Session"
    .SeriesCollection(2).Values = "Sheet1!Measure2"
    .HasLegend = True
    .Application.CommandBars("Format Object").Visible = False
    'Set all text to Arial'
    .ChartArea.font.Name = "Arial"
    'Set all text to black'
    .ChartArea.font.Color = RGB(0, 0, 0)
    For i = 1 To .SeriesCollection.Count
        'Set marker border color to black'
        .SeriesCollection(i).MarkerForegroundColorIndex = 1
        'Set marker border color to white'
        .SeriesCollection(i).MarkerBackgroundColorIndex = 2
        'Set size of all markers'
        .SeriesCollection(i).MarkerSize = 6
        'Set color of connecting lines to black'
        .SeriesCollection(i).Format.Line.ForeColor.RGB = RGB(0, 0, 0)
        'Set thickness of connecting lines'
        .SeriesCollection(i).Format.Line.Weight = 1
    Next i
End With

'Select a different marker for each series.'
i = 1
For Each ser In scol
    Set ser = ch.Chart.SeriesCollection(i)
    d = (((i - 1) Mod 4)   1)
        With ser
            .MarkerStyle = markers(d)
        End With
    i = i   1
Next ser

For Each axis In ch.Chart.Axes
    'Remove gridlines'
    axis.HasMajorGridlines = False
    axis.HasMinorGridlines = False
    'Set line color'
    axis.Format.Line.Visible = msoCTrue
    axis.Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
    axis.Format.Line.ForeColor.TintAndShade = 0
    axis.Format.Line.ForeColor.Brightness = 0
    'Set line thickness'
    axis.Format.Line.Weight = 1
    'Add tick marks'
    axis.MajorTickMark = xlTickMarkOutside
    'Set numbers on axes to size 10'
    axis.TickLabels.font.Size = 10
Next axis
    ch.Chart.Axes(xlValue).MinimumScale = -1
    ch.Chart.Axes(xlValue).CrossesAt = 0
    'Float the zero'
    ch.Chart.Axes(xlValue).CrossesAt = -0.2
    'Set x axis to max session value in column A'

Leave a Reply

Your email address will not be published. Required fields are marked *