How to create a dynamic charts with specific column inputs using vba?

Question

I'm trying to create a chart using VBA using the following code:

lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set chtRng = Range(Cells(1, 1), Cells(lr, lc))
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=chtRng
ActiveChart.SetElement (msoElementLegendBottom)
Set targetSht = ThisWorkbook.Worksheets("Test_graph")

The data is fetched from the database and the data gets flushed in A to D columns.Data While generating the graph using the above code, I get the following result:Current Results But, out of the fetched data, i want to skip the data in the column C and expect a result something likeExpected Results

Any sort of suggestion or solution is appreciated.

Regards.

PS: I'm new to this platform so kindly excuse me for the formatting.

Sphinx · Accepted Answer · 2019-04-04 09:38:48Z

Maybe like this

Option Explicit

Sub TestChart()

    Dim targetSht As Worksheet
    Dim LastRow As Long

    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

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

    With ActiveChart
        .SeriesCollection.Add Source:=ActiveSheet.Range(Cells(2, 2), Cells(LastRow, 2))
        With .SeriesCollection(1)
            .Name = ActiveSheet.Cells(1, 2)
            .XValues = ActiveSheet.Range(Cells(2, 1), Cells(LastRow, 1))
        End With
        .SeriesCollection.Add Source:=ActiveSheet.Range(Cells(2, 4), Cells(LastRow, 4))
        With .SeriesCollection(2)
            .Name = ActiveSheet.Cells(1, 4)
            .XValues = ActiveSheet.Range(Cells(2, 1), Cells(LastRow, 1))
        End With
        .HasTitle = True
        .ChartTitle.Text = "Some title"
        .SetElement (msoElementLegendBottom)
    End With
    Set targetSht = ThisWorkbook.Worksheets("Test_graph")

End Sub

Damian · Accepted Answer · 2019-04-04 09:21:29Z

0

I would advise you using the ChartObject to create charts like this. I'm gonna assume your data looks always the same so this would do it:

Option Explicit
Sub Test()

    Dim MyChart As ChartObject, ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1") ' change this for the name of the sheet you want the chart
    Set MyChart = ws.ChartObjects.Add(Left:=ws.Range("A1").Left, _
            Width:=ws.Range("A1:F1").Width, _
            Top:=ws.Range("A1").Top, _
            Height:=ws.Range("A1:A16").Height)
    With MyChart.Chart
        .SetSourceData Source:=ws.Range("H1:K16")
        .SeriesCollection(2).Delete
        .FullSeriesCollection(1).ChartType = xlLine
        .FullSeriesCollection(2).ChartType = xlLine
    End With

End Sub

As you can see I used references to insert the chart just were I wanted, .Left and top from a cell range for the position, a range of cells for both width and height.

If you just want to get ride of the second column it will be the SeriesCollection(2).

The output would look like:

Edit: The output looks like bars because I added later the code for making it like lines.

edited Apr 4, 2019 at 9:21

answered Apr 4, 2019 at 9:13

Damian

5,1741 gold badge12 silver badges22 bronze badges

5 Comments

P. Mehta Over a year ago

Looks good. And should work but the problem is, in your solution, the range is static and i need the range to be dynamic. Can you provide the solution for the same?

Damian Over a year ago

You just need to change my source range for yours and that's it... You already have that on your code.

Jon Peltier Over a year ago

Actually, AddChart2 (Excel 2013+) and AddChart (2007-2010) have the same optional Left, Top, Width, and Height arguments as the non-optional ones in ChartObjects.Add. The benefit of AddChart(2) is that they are optional; in addition, AddChart(2) allows you to specify the chart type, and AddChart2 the chart style. I've switched most of my production code to AddChart (not AddChart2 yet, because I still support Excel 2010).

Jon Peltier Over a year ago

Using AddChart would allow you to specify the line chart type up front, without having to change it later (which you also could have done for the chart as a whole instead of per series).

Jon Peltier Over a year ago

Also, I would change .SetSourceData Source:=ws.Range("H1:K16") to .SetSourceData Source:=ws.Range("H1:H16,"J1:K16") so I don't have to delete a series and lose its color in the chart series color sequence.

PASUMPON V N · Accepted Answer · 2019-04-04 09:30:17Z

0

Use Union for joining two ranges

Sub Program1()

Dim wb As Workbook
Dim ws As Worksheet
Dim Lastrow As Long
Dim Str1 As String
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")
Lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Set chtRng1 = ws.Range(ws.Cells(1, 1), ws.Cells(Lastrow, 1))
Set chtRng2 = ws.Range(ws.Cells(1, 2), ws.Cells(Lastrow, 2))
Set chtRng3 = ws.Range(ws.Cells(1, 3), ws.Cells(Lastrow, 3))
Set chtrng = Union(chtRng1, chtRng3)
End Sub

answered Apr 4, 2019 at 9:30

PASUMPON V N

1,1862 gold badges10 silver badges17 bronze badges

2 Comments

Damian Over a year ago

So... where are the dates on your chart? You are taking 2 columns out of 3, when the chart is getting 3 columns out of 4.

PASUMPON V N Over a year ago

@Damian i just sharing the concept, how to join two ranges , He can use ChtRng in this code to draw charts

Collectives™ on Stack Overflow

How to create a dynamic charts with specific column inputs using vba?

3 Answers 3

Comments

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related