Problem with empty cells when creating chart using vba

[Xavier]

Hello,

I am facing difficulties to create a chart from a sheet containing empty
cells (I use Excel 2003 SP2). My sheet looks similar to this:

A B C

1 0.1

2 0.2 25 34

3 0.3 23 27

4 0.4 24 6

5 0.5 25 16

6 0.6 27 21

7 0.7


Column A contains data (some depth in my case). Each cell in column A
contains data. Column B and C contain some measurements made at the depth
indicated in column A. I need to combine two graphics on one chart: the
content of B in function of A and the content of C in function of A.

The following code works fine:


With Graph

.ChartType = xlLine
.SetSourceData Source:=Sheets(MainSheet).Range("B2:C6"),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Sheets(MainSheet).Range("A2:A6")

(...)


The result of this code is a chart containing two lines, for X values rangin
from 0.2 to 0.6. Now, what I really need is to have the X values ranging
from 0.1 to 0.7 and keep the lines displayed for values 0.2 -> 0.6. So I
change my code to:


With Graph

.ChartType = xlLine
.SetSourceData Source:=Sheets(MainSheet).Range("B1:C7"),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Sheets(MainSheet).Range("A1:A7")

(...)


And in this case, only ONE line is displayed (values of column C) ???

I have been working on this for two nights and this is driving me nuts.

Any help would be appreciated.


Thanks,

Xavier

 

[Andy Pope]

Hi,

Excel is trying to guess your data layout and empty cells do not help.
Try using the Formula property of a series.

Sub X
Dim Graph As Chart
Dim MainSheet As String
Dim strXValues As String
Dim strValues As String

MainSheet = "Sheet2"
Set Graph = ActiveSheet.ChartObjects(1).Chart

With Graph
.ChartType = xlLine
'.SetSourceData Source:=Sheets(MainSheet).Range("A2:C7"), _
PlotBy:=xlColumns
.SeriesCollection.NewSeries
strXValues = "'" & MainSheet & "'!" & _
Sheets(MainSheet).Range("A2:A7").Address
strValues = "'" & MainSheet & "'!" & _
Sheets(MainSheet).Range("B2:B7").Address
.SeriesCollection(1).Formula = "=SERIES(," & _
strXValues & "," & strValues & ",1)"
.SeriesCollection.NewSeries
strValues = "'" & MainSheet & "'!" & _
Sheets(MainSheet).Range("C2:C7").Address
.SeriesCollection(2).Formula = "=SERIES(," & _
strXValues & "," & strValues & ",2)"
End With
End sub

Cheers
Andy

 

[Xavier]

Hi Andy,

Thanks a lot for your input, it works beautifully.
Do you have any pointer describing the technique you suggested (and why) ?

Thanks again,

Xavier