vba add trendline to chart in Excel

Q182. In Excel, how can I add Trendline to a chart using VBA?

For example, I have a Chart ("Chart1") for Sales data of the last 6 quarters - I would like to get a 2-Quarters moving average trendline added to the chart.

excel vba add trendline to chart

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub AddTrendLine()
  3. ActiveSheet.ChartObjects("Chart1").Chart.SeriesCollection(1).Trendlines.Add
  4. ActiveSheet.ChartObjects("Chart1").Chart.SeriesCollection(1).Trendlines(1).Select
  5. With Selection
  6. .Type = xlMovingAvg
  7. .Period = 2
  8. End With
  9. End Sub

Description:

a) Line 3 adds a Trendline to the Series Collection specified.

b) Line 6 specifies the Type of Trendline (could be xlExponential, xlLinear, xlLogarithmic,xlMovingAvg,xlPolynomial or xlPower)

c) Line 7 specifies the Peiod for moving average calculation.

Result after Macro execution:

excel excel vba add trendline to chart

 

You can find similar Excel Questions and Answer hereunder

1) How can I filter and copy only filtered data using VBA?

2) How can I protect / unprotect WorkSheet using VBA?

3) Import txt file in Excel

4) How can I get users to select a folder to save the output of my macro?

5) How do I enter a formula in a cell using VBA (using Absolute Reference)?

6) How can I add a legend to a chart using VBA?

7) How can I save a WorkSheet as a new WorkBook using VBA?

8) How do I enter a formula in a cell using VBA (using Relative Reference)?

9) How do I restrict user entry in ActiveX Text Box to just numeric values?

10) How can I list all files in a folder using VBA?

11) How can I get input from user through a prompt and assign user's input to a cell?

12) How can I loop through all WorkSheets and get their names using VBA?

13) How can I set the Source Data of charts using VBA?

14) How can I check if a file exists in a folder using VBA?

15) How to disable ability to insert Rows and Columns in Excel (using VBA)?