Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

How to create a line chart automatically - Excel VBA

Charts are another very important elements available in Excel. They play key role in data visualization. Charts have been important in comparing data and analytics. We often use them in reports and dashboards. Because they help to get data-driven insights for the users.Today I am going to show you how to create a chart automatically using VBA. Let’s consider this sample data.

This is a monthly profit of a shop. Now let’s create a chart to compare profit vs months. So months will be in x axis and profit will be in y axis. We can create the chart automatically using below code.

Sub CreateChart_Ex1()

Dim WS As Worksheet

Set WS = Worksheets("Sheet1")



WS.Shapes.AddChart2(227, xlLine).Select

End Sub

Note that you need to activate the sheet before select the range if you are in different sheet. WS.Activate doesn’t throw error even if you are in that same sheet. You will get this chart if you run above code.

However using this code, you can create a chart only in the worksheet where your data is in. Suppose you have data in one sheet and you need to create chart in another sheet. We have a solution for that too. Assume we have data in Sheet1 and we want to create chart in Sheet3. We can do it like this.

Sub CreateChart_Ex2()

Dim WS As Worksheet
Dim WS_New As Worksheet

Set WS = Worksheets("Sheet1")
Set WS_New = Worksheets("Sheet3")

WS_New.Shapes.AddChart2(227, xlLine).Select

ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$13")

End Sub

In above examples we created graphs for fixed range. How about if we require to create a chart for dynamic range. Then we have to find the last row of the data, assign it to a variable and then use that variable like this.

WS.Range("A1:B" & LastRow).Select

Also it is a good practice to define the variable at the beginning. You can define the LastRow as integer or long depending on the amount of rows you will have.

This post explains how to find the row number of the last non empty cell of a worksheet.

Best way to get the last row of any column of the excel sheet

So from this post we learnt how to create a chart with one series automatically. If you want to know how to create a chart with multiple series then check out this post.

Create line chart with multiple series using Excel VBA

Contact Form


Email *

Message *