Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column range...
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(NewSe t1), _ Sheets(ActiveSheet.Name).Range(NewSet2)) Range(CurLocation).Select End Sub ************************** |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column range...
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. "Joe" wrote: 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(NewSe t1), _ Sheets(ActiveSheet.Name).Range(NewSet2)) Range(CurLocation).Select End Sub ************************** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Program Column B to record numerical range based on number in colm | Excel Discussion (Misc queries) | |||
Match function...random search? | Excel Worksheet Functions |