Hi All
I have a predefined chart that I want to set the range collection
dynamically from VBA code. That works OK.

A mixed bar-line chart for 12 months of the year on the X-Axis. My problem
stems from variable number of values for each bar/line.

I set the ranges as follows.....

With chSales
.SeriesCollection(1).Values = shSales.Range("B29:B" & BMax)
.SeriesCollection(2).Values = shSales.Range("E29:E" & EMax)
.SeriesCollection(3).Values = shSales.Range("J29:J" & BMax)
.SeriesCollection(4).Values = shSales.Range("K29:K" & EMax)
.SeriesCollection(1).XValues = shSales.Range("A29:A40")
End With

The BMax and EMax ranges are variable length from 1 to 12 values. The X
values are fixed at 12. The problem arises in that the chart displays ONLY
the X-values up to the B or E max vlaues whichever is grerater. I need it
to show all 12 months regardless of how many values for the chart series
there are, and DO not want the lines in the chart to drop to zero for the
remainder of the chart.

It must be possible?

--
Cheers
Nigel

Plot 29:40 for each column of interest and do either of the following:

(1) Make sure the cells that do not contain data are truly empty (no
formula, no zero-length string (""), no nothing. In addition, ensure
that the Tools | Options... | Chart tab | 'Plot empty cells as' option
is set to 'Not plotted'.

(2) Replace the cells that should be empty with NA().

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , nigel-
says...
Hi All
I have a predefined chart that I want to set the range collection
dynamically from VBA code. That works OK.

A mixed bar-line chart for 12 months of the year on the X-Axis. My problem
stems from variable number of values for each bar/line.

I set the ranges as follows.....

With chSales
.SeriesCollection(1).Values = shSales.Range("B29:B" & BMax)
.SeriesCollection(2).Values = shSales.Range("E29:E" & EMax)
.SeriesCollection(3).Values = shSales.Range("J29:J" & BMax)
.SeriesCollection(4).Values = shSales.Range("K29:K" & EMax)
.SeriesCollection(1).XValues = shSales.Range("A29:A40")
End With

The BMax and EMax ranges are variable length from 1 to 12 values. The X
values are fixed at 12. The problem arises in that the chart displays ONLY
the X-values up to the B or E max vlaues whichever is grerater. I need it
to show all 12 months regardless of how many values for the chart series
there are, and DO not want the lines in the chart to drop to zero for the
remainder of the chart.

It must be possible?

Hi Tushar
Thanks for the advice.

In my range I have a conditional formula that looks like this
=IF(D20=0,"",D20*5) which results in the cell showing blank (empty(?)) but a
point shows on the chart .

If I change the formula to =IF(D20=0,NA(),D20*5) I end up with a nasty
#N/A in the cell which detracts from the table looks, but it does not show
the point on the chart.

Is there a way to create a blank cell AND not show the point if the cell has
a formula?

--
Cheers
Nigel



"Tushar Mehta" wrote in message
om...
Plot 29:40 for each column of interest and do either of the following:

(1) Make sure the cells that do not contain data are truly empty (no
formula, no zero-length string (""), no nothing. In addition, ensure
that the Tools | Options... | Chart tab | 'Plot empty cells as' option
is set to 'Not plotted'.

(2) Replace the cells that should be empty with NA().

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , nigel-
says...
Hi All
I have a predefined chart that I want to set the range collection
dynamically from VBA code. That works OK.

A mixed bar-line chart for 12 months of the year on the X-Axis. My
problem
stems from variable number of values for each bar/line.

I set the ranges as follows.....

With chSales
.SeriesCollection(1).Values = shSales.Range("B29:B" & BMax)
.SeriesCollection(2).Values = shSales.Range("E29:E" & EMax)
.SeriesCollection(3).Values = shSales.Range("J29:J" & BMax)
.SeriesCollection(4).Values = shSales.Range("K29:K" & EMax)
.SeriesCollection(1).XValues = shSales.Range("A29:A40")
End With

The BMax and EMax ranges are variable length from 1 to 12 values. The X
values are fixed at 12. The problem arises in that the chart displays
ONLY
the X-values up to the B or E max vlaues whichever is grerater. I need
it
to show all 12 months regardless of how many values for the chart series
there are, and DO not want the lines in the chart to drop to zero for
the
remainder of the chart.

It must be possible?

I knew the question about aesthetics was coming. :)

Just hide the #N/A through conditional formatting. See
http://groups-
beta.google.com/group/microsoft.public.excel.charting/browse_thread/thr
ead/e8029a50a033b833/3b17d65c02268e63?q=conditional+na+group:
*Excel*+author:tushar&rnum=1&hl=en#3b17d65c02268e6 3

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , nigel-
says...
Hi Tushar
Thanks for the advice.

In my range I have a conditional formula that looks like this
=IF(D20=0,"",D20*5) which results in the cell showing blank (empty(?)) but a
point shows on the chart .

If I change the formula to =IF(D20=0,NA(),D20*5) I end up with a nasty
#N/A in the cell which detracts from the table looks, but it does not show
the point on the chart.

Is there a way to create a blank cell AND not show the point if the cell has
a formula?

You guessed it right!

I had be playing around with conditinal formatting - your tip makes it work.
Pity I need to set each cell in the range one by one?

Thanks again

--
Cheers
Nigel



"Tushar Mehta" wrote in message
om...
I knew the question about aesthetics was coming. :)

Just hide the #N/A through conditional formatting. See
http://groups-
beta.google.com/group/microsoft.public.excel.charting/browse_thread/thr
ead/e8029a50a033b833/3b17d65c02268e63?q=conditional+na+group:
*Excel*+author:tushar&rnum=1&hl=en#3b17d65c02268e6 3

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , nigel-
says...
Hi Tushar
Thanks for the advice.

In my range I have a conditional formula that looks like this
=IF(D20=0,"",D20*5) which results in the cell showing blank (empty(?))
but a
point shows on the chart .

If I change the formula to =IF(D20=0,NA(),D20*5) I end up with a nasty
#N/A in the cell which detracts from the table looks, but it does not
show
the point on the chart.

Is there a way to create a blank cell AND not show the point if the cell
has
a formula?

No, you don't have to do one cell at a time. Select all cells and set
the conditional formatting formula to use a relative address as in
=ISNA(A1) rather than $A$1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , nigel-
says...
You guessed it right!

I had be playing around with conditinal formatting - your tip makes it work.
Pity I need to set each cell in the range one by one?

Thanks again

Brilliant!!

--
Cheers
Nigel



"Tushar Mehta" wrote in message
om...
No, you don't have to do one cell at a time. Select all cells and set
the conditional formatting formula to use a relative address as in
=ISNA(A1) rather than $A$1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , nigel-
says...
You guessed it right!

I had be playing around with conditinal formatting - your tip makes it
work.
Pity I need to set each cell in the range one by one?

Thanks again