Creating Chart with dynamic range size in VBA

WoodEXCEL

New Member
Joined
Jul 23, 2010
Messages
11
Here is the code.

The range will change for the column data. The data will always begin in
A21:B21 (two columns). How may rows will be dynamic (change with each VBA execution.

I keep getting an error relating the range set up below. How to get the syntax correct is my question.

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("TableQuery") _
.Range("A21", Range("A21:B21,End(xlDown)")), _
PlotBy:=xlColumns


Thank you much
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
ActiveChart.SetSourceData Source:=Sheets("TableQuery") _
.Range("A21:B" & Sheets("TableQuery").Range("A21").End(xlDown).Row), _
PlotBy:=xlColumns
 

chammy88

Board Regular
Joined
Jun 24, 2011
Messages
56
Try:

Code:
ActiveChart.SetSourceData Source:=Sheets("TableQuery") _
.Range("A21:B" & Sheets("TableQuery").Range("A21").End(xlDown).Row), _
PlotBy:=xlColumns

Hi Andrew,

I have a couple of questions with this bit of code:
1. How would it be modified if the columns required weren't next to each other eg. Column G as the x-axis values and column I as y-axis
2. How could you add a line series (values in column K) to the secondary y-axis. This is also a dynamic range.

Thanks in advance
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Try:

Code:
Sub Test()
    Dim LastRow As Long
    Dim Rng1 As Range
    Dim rng2 As Range
    Dim ShName As String
    With ActiveSheet
        LastRow = .Range("G" & .Rows.Count).End(xlUp).Row
        Set Rng1 = .Range("G2:G" & LastRow & ", I2:I" & LastRow)
        Set rng2 = .Range("K2:K" & LastRow)
        ShName = .Name
    End With
    Charts.Add
    With ActiveChart
        .ChartType = xlLine
        .SetSourceData Source:=Rng1
        With .SeriesCollection.NewSeries
            .Values = rng2
            .AxisGroup = 2
        End With
        .Location Where:=xlLocationAsObject, Name:=ShName
    End With
End Sub
 

chammy88

Board Regular
Joined
Jun 24, 2011
Messages
56
Andrew, that's absolutely brilliant. Thanks very much for helping me out.
 

chammy88

Board Regular
Joined
Jun 24, 2011
Messages
56

ADVERTISEMENT

Hi again,

I modified your code slightly as I simply need a column clustered now, so without the line part.
When I run code it creates two series on the chart, one for the cells A2:A6 and the other for the rows B2:B6 on the y-axis and the x-axis has the number 1 to 5. What I need is for A2:A6 (on x-axis) to be plotted against B2:B6 (on y-axis).

Thanks in advance for your help



Sub ParetoTop5Chart1()


Sheets("PivotTableCopy1").Select
With ActiveSheet
LastRow = .Range("A2:A6")
Set Rng1 = .Range("A2:A6", " B2:B6")
End With
Sheets("For DS Top5 Before 0600").Select


ActiveSheet.ChartObjects("DSPreviousShiftTop5Before0600").Activate
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Rng1
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Cause"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Duration (mins)"




End With


End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you already have a chart why do you need to change its Source? Here's what the macro recorder gave me when I created a chart that matches your description:

Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 01/08/2012 by Andrew
'
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B6"), PlotBy:= _
        xlColumns
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R6C1"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

Adapt it to suit.
 

chammy88

Board Regular
Joined
Jun 24, 2011
Messages
56
cool, thanks for that Andrew.

I manipulated it to the following code

Code:
Sub Pareto_Top5_Chart_1()


Sheets("PivotTableCopy2").Select


Sheets("For DS Top5 Before 0600").Select


ActiveSheet.ChartObjects("PreviousDSBefore0600").Activate
With ActiveChart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=Sheets("PivotTableCopy2").Range("A2:B6"), PlotBy:=xlColumns
        On Error Resume Next
        .SeriesCollection(2).Delete
        .SeriesCollection(1).Delete
        .SeriesCollection.NewSeries
        .SeriesCollection(1).Values = Sheets("PivotTableCopy2").Range("B2:B6")
        .SeriesCollection(1).XValues = Sheets("PivotTableCopy2").Range("A2:A6")
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Cause"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Duration (mins)"




End With


End Sub

For some reason the graph still adds a Series1 and a Series2 when it generates so I delete them both and then add Series1 again.
 

Ken del

New Member
Joined
Aug 12, 2014
Messages
1
Try:

Code:
Sub Test()
    Dim LastRow As Long
    Dim Rng1 As Range
    Dim rng2 As Range
    Dim ShName As String
    With ActiveSheet
        LastRow = .Range("G" & .Rows.Count).End(xlUp).Row
        Set Rng1 = .Range("G2:G" & LastRow & ", I2:I" & LastRow)
        Set rng2 = .Range("K2:K" & LastRow)
        ShName = .Name
    End With
    Charts.Add
    With ActiveChart
        .ChartType = xlLine
        .SetSourceData Source:=Rng1
        With .SeriesCollection.NewSeries
            .Values = rng2
            .AxisGroup = 2
        End With
        .Location Where:=xlLocationAsObject, Name:=ShName
    End With
End Sub


Hi Andrew,

I have a similar requirement that requires the dynamic range.

This is my recorded micro.

ActiveSheet.range("A5").Select
range(Selection, Selection.End(xlDown)).Select
range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=range("SODL_Range!$A$5:$W$8801")


i reckon i need to make the range selection(highlight in bold) to be on activesheet and reflect my current selection so as to make it portable to other sheet?

Could you advice how i could do that?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,837
Messages
5,833,918
Members
430,244
Latest member
Ireland1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top