Hiding data points in Excel line charts

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().Points()) which deals with this...

Answer 1

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

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

Answer 2

As a general tip: If you know how to do something in excel, but don't know how to do it in VBA you could just record a macro and look at the recorded VBA-code (works at least most of the time)

Answer 3

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)

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

Answer 4

This is probably too late to be of assistance but the answer by SpyJournal, whilst easy and elegant,is slightly incorrect as it is necessary to omit the quotes around #N/A

Answer 5

Actually if you are going to use SpyJournal's answer it has to be =IF(b2=0,NA(),b2), otherwise Excel just recognizes it as text, not as an 'official' #N/A