Range related for chart

[Guest]

Hi,

Columns B and C are dynamic (# values/#entries will vary) and the code
should set the source data accordingly for the graph.

In the code below, it changes the value of Column B ONLY, But Column C is
not considered it stops way above the enod of value (example if the last cell
with value is C45, it might stop at C40 etc). Appreciate any help??

*******************************
Sub SetDataSource()

Dim NewSet1 As String
Dim NewSet2 As String
Dim CurLocation As String
CurLocation = ActiveCell.Address
NewSet1 = "B2:" & Range("B2").End(xlDown).Address
NewSet2 = "C2:" & Range("C2").End(xlDown).Address

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData _
Source:=Union(Sheets(ActiveSheet.Name).Range(NewSet1), _
Sheets(ActiveSheet.Name).Range(NewSet2))

Range(CurLocation).Select

End Sub

**************************

 

[Guest]

Try (take from row 2 to last row with values
if there is som values down the sheet u dont want
in ur chart, then change 65500 to the last row u want in


Sub SetDataSource()

Dim NewSet1 As String
Dim NewSet2 As String
Dim CurLocation As String
CurLocation = ActiveCell.Address
NewSet1 = "B2:" & Range("B65500").End(xlUp).Address
NewSet2 = "C2:" & Range("C65500").End(xlUp).Address

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData _
Source:=Union(Sheets(ActiveSheet.Name).Range(NewSet1), _
Sheets(ActiveSheet.Name).Range(NewSet2))

Range(CurLocation).Select

End Sub




"Joe" skrev:

 

[Guest]

Hi,

Thanks for the input/suggestion. I have a graph based on this and this will
skew up my graph..

Thanks & Regards