Results 1 to 8 of 8

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

  1. #1
    New Member
    Join Date
    Feb 2013
    Location
    Boulder, CO
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    New Member
    Join Date
    Feb 2013
    Location
    Boulder, CO
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

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

    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 "".
    Microsoft MVP - Excel

  4. #4
    New Member
    Join Date
    Feb 2013
    Location
    Boulder, CO
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

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

    You may find this helpful:

    AJP Excel Information - Broken lines
    Microsoft MVP - Excel

  6. #6
    New Member
    Join Date
    Feb 2013
    Location
    Boulder, CO
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    ActiveChart.SeriesCollection(1).Points(5).Format.Line.Visible = msoFalse ActiveChart.SeriesCollection(1).Points(8).Format.Line.Visible = msoFalse

    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...

  7. #7
    Board Regular
    Join Date
    Feb 2011
    Location
    Brampton, Ontario
    Posts
    500
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    xl2007 - Windows-7 & XP

    Does xl hate the number 255 ?
    biggest limitation - drives me insane

  8. #8
    Board Regular
    Join Date
    Feb 2011
    Location
    Brampton, Ontario
    Posts
    500
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    .Points(ii).Border.ColorIndex = 3
    xl2007 - Windows-7 & XP

    Does xl hate the number 255 ?
    biggest limitation - drives me insane

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •