Set scatter chart in VBA

Question

i would like to implement simple VBA code which draws scatter chart between two variable, let us suppose that our data is given in range of A2:B51, with help of stackoverflow, i found code which draws scatter chart and it is given here :

scatter chart

i am interested if we can change code a bit so that x values limit can be automatically corrected in range of (xmin,xmax) and also between them add trendline?

code is given here

Private Sub scatter_chart_Click()
Dim xrng As Range
Dim yrng As Range


Set xrng = Range("A2:A51")
Set yrng = Range("B2:B51")

Dim Chart1 As Chart
    Set Chart1 = Charts.Add

 With Chart1
        .ChartType = xlXYScatter
        .SeriesCollection.NewSeries
        'Change to what your series should be called
        .SeriesCollection(1).Name = "=""Price Versus Demand"""
        .SeriesCollection(1).XValues = xrng
        .SeriesCollection(1).Values = yrng
End With


End Sub

Answer 1

Please try.

Option Explicit
Private Sub scatter_chart_Click()
    Dim xrng As Range
    Dim yrng As Range
    Set xrng = Range("A2:A51")
    Set yrng = Range("B2:B51")
    Dim Chart1 As Chart
    Set Chart1 = Charts.Add
    With Chart1
        .ChartType = xlXYScatter
        .SeriesCollection.NewSeries
        'Change to what your series should be called
        .SeriesCollection(1).Name = "=""Price Versus Demand"""
        .SeriesCollection(1).XValues = xrng
        .SeriesCollection(1).Values = yrng
        .Axes(xlCategory).MinimumScale = Application.Min(xrng)
        .Axes(xlCategory).MaximumScale = Application.Max(xrng)
        .FullSeriesCollection(1).Trendlines.Add Type:=xlLinear, _
        Forward:=0, Backward:=0, DisplayEquation:=0, DisplayRSquared:=0, _
        Name:="Linear (Price Versus Demand)"
    End With
End Sub

Microsoft documentation:

Axis.MinimumScale property (Excel)

Trendlines object (Excel)