I'd like to NOT show zero values on a chart and despite some good information
via this site (ie ToolsOptionsChart the "plot empty cells" GUI is
disabled), I think I may need another method via VBA, so....

I have a range named "ChartData" and it refers to the populated cells a1:b7.

What I need to do is, if cells a5:b7 are blank, systematically redefine the
"ChartData" range to then refer to a1:b4. (via sorting the range, I can
ensure all the blank data is at the bottom of the ranges).

If this can be accomplished, then the chart data range will only have data
and my issue with NOT showing zero values will be resolved.

If anyone can either provide the code to accomplish this or suggest a more
efficient alternative, please help.

Any and All Help Will Be Appreciated - Thanks In Advance

instead of sorting and renaming you could filter the data.
faik hidden lines (filtered out) will not appear on the chart.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


MWS wrote in

I'd like to NOT show zero values on a chart and despite some good
information via this site (ie ToolsOptionsChart the "plot empty
cells" GUI is disabled), I think I may need another method via VBA,
so....

I have a range named "ChartData" and it refers to the populated cells
a1:b7.

What I need to do is, if cells a5:b7 are blank, systematically
redefine the "ChartData" range to then refer to a1:b4. (via sorting
the range, I can ensure all the blank data is at the bottom of the
ranges).

If this can be accomplished, then the chart data range will only have
data and my issue with NOT showing zero values will be resolved.

If anyone can either provide the code to accomplish this or suggest a
more efficient alternative, please help.

Any and All Help Will Be Appreciated - Thanks In Advance

sort the data and use defined names for your data source

Insert =Name=Define

Name:= listA
Refersto = =Offset(Sheet1!$A$1,0,0,Count(Sheet1!$B$1:$B$7),1)

Add Button

Name:= listB
Refersto = =Offset(Sheet1!$B$1,0,0,Count(Sheet1!$B$1:$B$7),1)

Add Button

Use the names ListA and ListB as your data sources.

--
Regards,
Tom Ogilvy

"MWS" wrote in message
...
I'd like to NOT show zero values on a chart and despite some good
information
via this site (ie ToolsOptionsChart the "plot empty cells" GUI is
disabled), I think I may need another method via VBA, so....

I have a range named "ChartData" and it refers to the populated cells
a1:b7.

What I need to do is, if cells a5:b7 are blank, systematically redefine
the
"ChartData" range to then refer to a1:b4. (via sorting the range, I can
ensure all the blank data is at the bottom of the ranges).

If this can be accomplished, then the chart data range will only have data
and my issue with NOT showing zero values will be resolved.

If anyone can either provide the code to accomplish this or suggest a more
efficient alternative, please help.

Any and All Help Will Be Appreciated - Thanks In Advance

Thank You - There's Always A Work-Around - Take Care

"keepITcool" wrote:


instead of sorting and renaming you could filter the data.
faik hidden lines (filtered out) will not appear on the chart.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


MWS wrote in

I'd like to NOT show zero values on a chart and despite some good
information via this site (ie ToolsOptionsChart the "plot empty
cells" GUI is disabled), I think I may need another method via VBA,
so....

I have a range named "ChartData" and it refers to the populated cells
a1:b7.

What I need to do is, if cells a5:b7 are blank, systematically
redefine the "ChartData" range to then refer to a1:b4. (via sorting
the range, I can ensure all the blank data is at the bottom of the
ranges).

If this can be accomplished, then the chart data range will only have
data and my issue with NOT showing zero values will be resolved.

If anyone can either provide the code to accomplish this or suggest a
more efficient alternative, please help.

Any and All Help Will Be Appreciated - Thanks In Advance