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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Try:

Code:
ActiveChart.SetSourceData Source:=Sheets("TableQuery") _
.Range("A21:B" & Sheets("TableQuery").Range("A21").End(xlDown).Row), _
PlotBy:=xlColumns
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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
Back
Top