CODEWITHSUNDEEP
vbaexcel
av abhishiek
av abhishiek

Reputation: 667

Passing non sequential columns in a range in VBA to create dynamic charts

I am a newbie to VBA and was trying to create a dynamic range for simple trendline chart, initially to access the components , I recorded a Macro while creating the chart so that I can edit the source range later to be altered dynamically

Sub Macro10()
'
' Macro10 Macro
'
    Range("B8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Shapes.AddChart2(227, xlLineStacked).Select
    ActiveChart.SetSourceData Source:=Range("Sheet10!$B$8:$C$16808")

    'ActiveChart.SetSourceData Source:=Range("Sheet6!$B$8:$C$16808")
End Sub

My source data is in separately sheet(Sheet6)and I want to refer it dynamically to build my chart, In source data the required columns are not next to each other, so I was trying to alter the Range (in the commented Source Range) to refer to those columns, any ideas on how to achieve it ?

Also I need it to be dynamic for both the columns which I want to pass in the range to build a time series kind of chart.

I tried using one of the answers mentioned below, but my chart was not proper , does the CuurentRegion Property select all the columns or only the relevant columns

 data.


Sub Macro10()
'
' Macro10 Macro
'

'



    Dim R As Range

    Set R = Sheet6.Range("C1").CurrentRegion 'Dates for Time Series
    Set R = Union(R, Sheet6.Range("F1").CurrentRegion) ' Profits for those Dates

    'Range("B8").Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Range(Selection, Selection.End(xlToRight)).Select
    'ActiveSheet.Shapes.AddChart2(227, xlLineStacked).Select
    'ActiveChart.SetSourceData Source:=Range("Sheet10!$B$8:$C$16808")
    ActiveSheet.ChartObjects("Chart 4").Activate
     ActiveChart.SetSourceData Source:=R

    'ActiveChart.SetSourceData Source:=Range("Sheet6!$B$8:$C$16808")


End Sub

Upvotes: 0

Views: 1546

Answers (1)

Sam
Sam

Reputation: 5721

Working with Selection is generally not a good approach. Build your cell selection in a range variable instead.

Dim R as Range
Set R = Range("B8", Range("B8").End(xlDown).End(xlToRight))
Set R = Union(R, Range("A1:A9")
Set R = Union(R, Range("X1:X9")

Now you can pass your range like this:

ActiveChart.SetSourceData Source:=R

Upvotes: 1

Related Questions