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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,680
Messages
5,512,825
Members
408,916
Latest member
juliesmithing

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top