How to create a gap in a line chart for empty data when the data series is populated with an array?

[TGib]

Hi,

If this has been answered elsewhere please feel to provide link to the thread. I have been searching for a while but haven't been able to find an appropriate answer.

Problem
I'm trying to create a line chart using "Scatter with Straight Lines" that will create a gap in the line if Y values don't exist for a given X value. The chart data series are arrays of values calculated in VBA and assigned to the chart. I have found the gaps will generate correctly if the data series were referencing a Range on a worksheet but I haven't had success if the data series is an Array.


Solution Attempts
To create the graphs, I have tried placing the following in the array where the gaps should occur.
""
#N/A
NA()
CVErr(xlErrNA)
vbNullString
I also tried changing to chart type "Line"

I'm trying to avoid printing the VBA values to a worksheet and then referencing the worksheet range. Because the workbook I'm creating is for making various charts from data the user provides. The charts will be created across multiple worksheets that the user defines and the number of charts, as well as, the order the charts are created in is not known ahead of time.


Any help is greatly appreciated! Even if "not possible" is the answer.

 

[TGib]

...forgot to include that I'm using Excel 2010.

 

[Andrew Poulsom]

Welcome to MrExcel.

It seems that the 'Show empty cells as' setting only applies to data on a worksheet (in cells ). If you convert the SERIES formula to arrays, the blanks become zero and can't be overwritten with "".

 

[TGib]

Thanks.

Yeah, that is what I was finding. I also found that "#N/A" skipped the empty array values but connected a line from the last actual value to next actual value. This could be interrepted as having a value in the empty array when there isn't one though.

 

[Andrew Poulsom]

You may find this helpful:

AJP Excel Information - Broken lines

 

[TGib]

Thanks for the link. That is an interesting approach, although kind of cumbersome to add the additional data series. But it made me think...could the line be removed through "Format" options? Turns out, yes it can!


Here are the steps that worked for me:

Non-VBA Method:​

1. Click once on line section to remove from the chart, pause, then click it again. Right-click and choose "Format Data Point". There are other ways to get to this dialog box if you know them.
2. Select "Line Color" > "No Line"

VBA Method:
1. Identify the array index location(s) of the first actual value that comes after the "#N/A" value(s) in the data series array. "#N/A" put in the data series array when no value exists.​

In the following example the array indexes you would want to identify are for numbers "41" and "51". Which are array indexes "5" and "8".

=SERIES("series name",{10,11,12,13,14,15,},{31,32,#N/A,#N/A,41,42,#N/A,51,52},1)

2. Then turn off the visibility of those line setions.​

Continuing with example above:​

[INDENT=2]ActiveChart.SeriesCollection(1).Points(5).Format.Line.Visible = msoFalse
ActiveChart.SeriesCollection(1).Points(8).Format.Line.Visible = msoFalse
[/INDENT]

I apologize if I'm not using the code tags properly, I wasn't able to load "HTMLMaker20101230".


Thank you for pointing me to that article, I hadn't tried searching with the keyword "broken". I tried "gap", "missing", "empty". It's all in the keyword...

 

[Rasm]

This code is works well (thank you - very helpful) -- However I do want the line to show -- but I want to connect the points using a different colorIndex -- any help in how to connect the dots where I have another color -- but now I also want to change the color of the connecting line -- but only for the point 5-8

ActiveChart.SeriesCollection(1).Points(5).Format.Line.Visible = msoFalse
ActiveChart.SeriesCollection(1).Points(8).Format.Line.Visible = msoFalse

 

[Rasm]

I found the answer -- here it is - so nobody has to spend time on find the answer

.Points(ii).Border.ColorIndex = 3