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

TGib

New Member
Joined
Feb 20, 2013
Messages
4
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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 "".
 
Upvote 0
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.
 
Upvote 0
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".
Code:
=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:​
Code:
[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...:)
 
Upvote 0
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
 
Upvote 0
I found the answer -- here it is - so nobody has to spend time on find the answer

.Points(ii).Border.ColorIndex = 3
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top