Excel VBA绘制XY散点图时X轴刻度间隔不符合需求

你当前代码生成的X轴刻度不符合预期，核心是两个遗漏：

• 未对X轴的刻度规则做强制配置，Excel会按默认逻辑自动生成任意间隔的刻度，完全不匹配你要求的0.03步长规则
• 代码中声明的ColumnARngData、ColumnBRngData两个数据范围对象全程未赋值，运行时会触发对象报错，无法正确加载绘图数据

修正内容 #

• 在X轴配置块中新增3个关键属性设置，强制固定刻度起始值、步长、终止值逻辑
• 补全数据范围的自动识别逻辑，按列内最后非空行匹配实际绘图数据
• 移除原代码中声明但从未使用的冗余变量LastRowOfG

修正后可直接运行的代码 #

Sub plottinggraph()
    Dim LastRowOfA As Long, LastRowOfB As Long
    Dim ColumnARngData As Range, ColumnBRngData As Range
    Dim xAxes As Axis, yAxes As Axis
    Dim srsNew As Series
    Dim cht As Shape

    ' 自动匹配A、B列的实际数据范围，若数据无表头可将起始行号改为1
    LastRowOfA = Cells(Rows.Count, "A").End(xlUp).Row
    LastRowOfB = Cells(Rows.Count, "B").End(xlUp).Row
    Set ColumnARngData = Range("A2:A" & LastRowOfA)
    Set ColumnBRngData = Range("B2:B" & LastRowOfB)

    Set cht = ActiveSheet.Shapes.AddChart
    With cht.Chart
        .HasTitle = True
        .ChartTitle.Text = "X VS Y"
        .ChartType = xlXYScatterLinesNoMarkers
        
        Set srsNew = .SeriesCollection.NewSeries
        .SeriesCollection(1).Name = "X, Y"
        .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 0, 0)
        With srsNew
            .XValues = ColumnARngData
            .Values = ColumnBRngData
        End With
        
        Set xAxes = .Axes(xlCategory, xlPrimary)
        With xAxes
            .HasTitle = True
            .AxisTitle.Text = "X"
            ' *核心刻度配置*，直接生成0.00、0.03、0.06...的刻度序列
            .MinimumScale = 0
            .MajorUnit = 0.03
            ' 自动对齐最大刻度到步长的整数倍，避免最后一个刻度截断
            .MaximumScale = WorksheetFunction.Ceiling(ColumnARngData.Cells(ColumnARngData.Cells.Count).Value, 0.03)
        End With
            
        Set yAxes = .Axes(xlValue, xlPrimary)
        With yAxes
            .HasTitle = True
            .AxisTitle.Text = "Y"
        End With
    End With
End Sub

自定义调整提示 #

如果需要修改X轴的起始值或终止值，直接修改MinimumScale、MaximumScale的赋值即可；如果不需要自动对齐最大刻度，删除对应行即可让Excel按数据实际值终止轴范围。

内容的提问来源于stack exchange，提问作者Chewjunnie