Hiding data points in Excel line charts

By : d91-jal
Source: Stackoverflow.com
Question!

It is obviously possible to hide individual data points in an Excel line chart.

  • Select a data point.
  • Right click -> Format Data Point...
  • Select Patterns
  • Tab Set Line to None

How do you accomplish the same thing in VBA? Intuition tells me there should be a property on the Point object Chart.SeriesCollection(<index>).Points(<index> which deals with this...

By : d91-jal


Answers

I tried "#N/A" with quotes in Excel 207 and as a result the data point is shown like a zero in the graph. It Works without the quotes.



Yes. It doesn't have to have the quotes to be a true not available cell content but for me N/A still plot as 0 in my charts.

The only way I can get it not to plot is to have the cell blank.

By : Garrydene


There is a Non VBA solution as well that can also be controlled from the VBA code as well. In excel a data point represented by a #N/A will not display. Thus you can use a formula - the easiest is an IF function - that returns an #N/A as text in the graph data. This data point will then not display which means you don't need to try and manipulate the format for it.

An example is simply to generate your graph data in a table, and then replicate it below with a formula that simply does this

=If(B2=0,"#N/A",B2)

This works when you want to stop line charts from displaying 0 values for example.



This video can help you solving your question :)
By: admin