Continuity of the series

[Guest]

Hi,

I have a series in the graph, a column B starting from row 1 to 20. These
values are populated from other source and not always there will be value in
each cell, however the continuity of the graph should be kept. Because of the
no values in the cell the series graph is incomplete. CAn we programatically
complete the series? Suppose this the the series:
Col B
90.00
95.00

90.00
95.00

90.00

10.00
25.00
70.00


80.00

90.00

90.00
98.00
End
The series in the graph is getting truncated after first break in the values.
Pls help me.
*******************
Sample code:
Sub GenerateGraph()

Dim NewSet As String
Dim NewSet1 As String
Dim CurLocation As String

CurLocation = ActiveCell.Address


NewSet = "B2:" & Range("B2").End(xlDown).Address
NewSet1 = "C2:" & Range("C2").End(xlDown).Address


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

Range(CurLocation).Select

End Sub

 

[Guest]

Hi Joe,

A. If your series always stays in the cells B1:C20, use the following:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Range("B2:C20")

B. If you always want to include 20 entries in the series, but don't know
in advance where the series will be populated, use the following:

NewSet = Range(ActiveCell.Address, ActiveCell.Offset(19, 1).Address).Address
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Range(NewSet)

C. If your series goes from a current cell down to the cell one above and
one to the right of "End", use the following:

Dim c As Range
Set c = Cells.Find(What:="End", LookIn:=xlValues, lookat:=xlWhole)
NewSet = Range(ActiveCell.Address, c.Offset(-1, 1).Address).Address
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Range(NewSet)

Hope it helps,
Ilya

 

[Guest]

Hi Ilya,

Thanks so much for the solution.

My code is like below which is working fine, and I want to add the
feature/functionality only for Column G:
There are 6 columns in the code:
My code:
*******************************
Sub GenerateGraph()

Dim NewSet As String
Dim NewSet1 As String
Dim NewSet2 As String
Dim NewSet3 As String
Dim NewSet4 As String
Dim NewSet5 As String


Dim CurLocation As String

CurLocation = ActiveCell.Address

Sheets("Report").Select
Columns("N:T").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Graph").Select
Columns("A:G").Select

ActiveSheet.Paste

NewSet = "B2:" & Range("B2").End(xlDown).Address
NewSet1 = "C2:" & Range("C2").End(xlDown).Address
NewSet2 = "D2:" & Range("D2").End(xlDown).Address
NewSet3 = "E2:" & Range("E2").End(xlDown).Address
NewSet4 = "F2:" & Range("F2").End(xlDown).Address
NewSet5 = "G2:" & Range("G2").End(xlDown).Address

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


ActiveChart.SeriesCollection(1).Name = Range("Graph!B1")
ActiveChart.SeriesCollection(2).Name = Range("Graph!C1")
ActiveChart.SeriesCollection(3).Name = Range("Graph!D1")
ActiveChart.SeriesCollection(4).Name = Range("Graph!E1")
ActiveChart.SeriesCollection(5).Name = Range("Graph!F1")
ActiveChart.SeriesCollection(6).Name = Range("Graph!G1")


Range(CurLocation).Select

End Sub
*************************
How do I take care of the broken range becaouse of unavailable data only for
Column G?

Thanks somuch in advance.

Regards

 

[Guest]

Hi Joe,

If you want to use the same number of entries in your "G" series as in
another continues series - "B", use the following:
NewSet5 = "G2:G" & Range("B2").End(xlDown).Row

If the number of entries in "G" series that you want to display is not
related to other series, use the following:
NewSet5 = "G2:G" & Range("B65536").End(xlUp).Row
This approach assumes max # of rows 65536. This number will increase in
Excel 2007.

Ilya