Column range...

[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), as I do not know the cell#
(last cell with value), I do not want to hardcode to value to 65000, which
will skew my graph.

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]

If you know that only data is in a column (i.e., no comments or non-graphable
info in that column) try something like:

NewSet2 = "C2:" & range("C" & Rows.Count).End(xlUp).Address

The Rows.Count keeps you from hard coding the 65000 and makes it flexible
enough to move to other versions of Excel. Nice trick that I was just clued
into here in these forums recently.