Sub MyMacro()
    Dim rng As Range
    Dim plot As Chart
    Set rng = Sheets(1).UsedRange
    Dim x As Range
    Dim y As Range
    Set x = rng.Columns("A")
    Set y = rng.Columns("B")
    Set plot = Charts.Add
    plot.ChartType = xlXYScatter
    Dim s As Series
    Set s = plot.SeriesCollection.NewSeries()
    s.XValues = x
    s.Values = y
    Dim t As Trendlines
    Set t = s.Trendlines
    t.Add xlLinear
End Sub