skipping blanks (totally!) in column charts

[man_o...@my-deja.com]

I want my column chart to totally ignore the blanks. It always leaves
a gap and includes an x-axis value for them (it plots them as a zero-
height bar).

any suggestions?

Sent via Deja.com
http://www.deja.com/

[Peltier]

Hi man-o-war -

When you select the plot ranges, use the control key to allow multiple
area selections. Don't select the records with blanks. If you want a
macro solution, read on. I posted this several months back. A reader
had data set up like this, and wanted to skip zeros. Don't be confused,
Column B has data for Category A, so they are all off by one column.

Categories A B C D E F G H I
Value1 1 5 0 0 1 3 2 7 8
Value2 2 3 0 0 1 1 2 6 5

Select A1:J1 ("Categories" to "I"), go to Insert menu | Name | Create |
Left Column. Now B1:J1 are defined by the name "Categories".

Paste this code into a regular module in that worksheet (Alt-F11 to
open
Visual Basic Editor, right click on workbook in Project browser in upper
left sector, Insert --> module):

' Code Starts '''''''''''''''''''''''''''''''''

Option Explicit

Sub skip_zero()

Dim cel As Range
Dim nonzeroCats As Range
Dim nonzeroVal1 As Range
Dim nonzeroVal2 As Range
Set nonzeroCats = Nothing
Set nonzeroVal1 = Nothing
Set nonzeroVal2 = Nothing

For Each cel In ActiveSheet.Range("Categories")
If cel.Offset(1, 0).Value <> 0 Then
If nonzeroCats Is Nothing Then
Set nonzeroCats = Range(cel.Address)
Set nonzeroVal1 = Range(cel.Offset(1, 0).Address)
Set nonzeroVal2 = Range(cel.Offset(2, 0).Address)
Else
Set nonzeroCats = Union(nonzeroCats, _
Range(cel.Address))
Set nonzeroVal1 = Union(nonzeroVal1, _
Range(cel.Offset(1, 0).Address))
Set nonzeroVal2 = Union(nonzeroVal2, _
Range(cel.Offset(2, 0).Address))
End If
End If
Next 'cel
'nonzeroCats.Select

ActiveWorkbook.Names.Add Name:="nonzeroCats", _
RefersToR1C1:=nonzeroCats
ActiveWorkbook.Names.Add Name:="nonzeroVal1", _
RefersToR1C1:=nonzeroVal1
ActiveWorkbook.Names.Add Name:="nonzeroVal2", _
RefersToR1C1:=nonzeroVal2

End Sub

' Code Ends '''''''''''''''''''''''''''''''''''

Now you have three ranges defined, nonzeroCats, nonzeroVal1,
nonzeroVal2, corresponding to rows A, B, and C without zeros in B and C.

Select A1:J3 (the entire range), and make the bar chart you want (even
though the zeros are there). Right click the plot and select Source
Data. Series Value1 is selected. Change Category (X) axis labels from
Sheet1!B1:J1 to Sheet1!nonzeroCats (replace Sheet1 by your sheet name).
Change Values from Sheet1!B2:J2 to Sheet1!nonzeroVal1. Select series
Value2. Category (X) axis labels changes to Book1!nonzeroCats (or your
workbook name!nonzeroCats). Change Values from Sheet1!B3:J3 to
Sheet1!nonzeroVal2. Click Okay.

Voila! When the data changes and the zeros relocate, just rerun the
macro, no need to update the chart by hand.

If your series run in columns, that's a quick fix to the process and the
macro. If you want to see this in a workbook, e-mail me off-line, and
ask for "OmitZeros.xls".

- Jon
_______