CODEWITHSUNDEEP
excelvba
P. Mehta
P. Mehta

Reputation: 11

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

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.

Upvotes: 0

Views: 1490

Answers (3)

Sphinx
Sphinx

Reputation: 660

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

Upvotes: 1

PASUMPON V N
PASUMPON V N

Reputation: 1186

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

Upvotes: 0

Damian
Damian

Reputation: 5174

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:

Output

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

Upvotes: 0

Related Questions