Problem with empty cells when creating chart using vba

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

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 wrote:
> 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, Microsoft MVP - Excel
http://www.andypope.info

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


"Andy Pope" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>> 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, Microsoft MVP - Excel
> http://www.andypope.info