Hiding data points in Excel line charts

Question

Obviously, you can hide individual data points in an Excel line chart.

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

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

Answer 1

"Describe it to a teddy bear" works almost every time ...

You need to go to the child object Border of the Point object and set its LineStyle to xlNone.

Answer 2

Actually, if you are going to use the SpyJournal answer, it should be =IF(b2=0,NA(),b2) , otherwise Excel just recognizes it as text, and not as "official" #N/A

Answer 3

As a general tip: if you know how to do something in excel but don’t know how to do it in VBA, you can just write a macro and look at the written VBA code (works at least most of the time)

Answer 4

There is also a Non VBA solution that can also be controlled using VBA code. In excel, the data point represented by # N / A will not be displayed. Thus, you can use the formula - the simplest IF function, which returns the text #N/A as the text in the graph data. This data point will not be displayed, which means that you do not need to try to manipulate the format for it.

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

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

This works if you want the chart lines to display, for example, the values ​​0.

Answer 5

It may be too late to help, but SpyJournal's answer, although simple and elegant, is a bit incorrect, as you must omit the quotation marks around # N / A

Answer 6

Yes. The quotes do not need to be the true inaccessible contents of the cell, but for me, N / A is still plotted as 0 in my graphs.

The only way I can get it to not draw is to clear the cell.

Answer 7

I tried "# N / A" with quotes in Excel 207, and as a result, the data point is displayed as zero on the graph. It works without quotes.