Ignoring Null Values when Plotting an Excel 2003 Bar Chart

I have some data that I'm plotting on a bar chart and I'm trying to "HIDE" the columns with zero or null values. Basically, if the column is blank, I don't want a "gap" on the chart. Is there a way to do this? I'm not getting this to work. Excel Forum Charting Help.xls

Hello -

Here is a sample workbook that I'm working from.
I'm not very familiar with macro's.

Excel Forum Charting Help.xls

Hi,

Sorry it took so long. Please try the attached workbook, which should do what you want.

Here are the instructions, which are also included on Sheet 'Notes' in the Workbook:
Chart creation Instructions:
1. The chart will be created on the same Sheet as the 'Data Table' which MUST BE the Active Sheet.
No Charts are DELETED. They are placed ON TOP of existing Charts.
2. Create an extra column in the data table called 'Bar Color'. Color each cell in the 'Bar Color' column with the color required for that item. This is needed because the default colors don't seem to be used.
3. Start out on the sheet that contains the 'Data Table'.
4. Run Macro 'CreateBarChartWithContiguousBars'. A prompt will appear, and selection MUST BE MADE to include 'Product Name' at the TOP LEFT and 'Bar Color' at the BOTTOM RIGHT.
5. A Chart will be created that looks like your original chart in the original location on the Active Sheet.
6 A SHADOW data table is created starting on Row 71. IT MUST NOT BE DELETED unless the Chart is deleted.
7. To change a value in the Chart, put a new value in the ORIGINAL DATA TABLE, EXCEPT that
a cell that was formerly BLANK or ZERO can not be changed. A new chart is required.
8. The 'Bar Color' column in the ORIGINAL data table can be deleted after the Chart is created.

Lewis

This works great! Thanks for your help. No worries on timeframe.

I'm glad it worked out for you.

The challenging part was not hiding the zero/null values, but making the rest of the chart look identical to your original.

Lewis

Andy Pope has an alternate solution that DOES NOT required VBA, but requires modification of data tables.
http://www.excelforum.com/excel-char...sing-data.html

Lewis