Charts in EXcel 2016 with Jabs for #N/A

Copper Contributor

i used to have an Excel Sheet utilizing VBA and collecting data from another sheets arrange it and so on, as a part of the sheet i use it to draw charts by just clicking an VBA button created by me for a certain range of values collected from another sheet.

those values were having some "text" containing cells among them so in order to go around it in office 2013 i just make a formula to change those "text" into a #N/A in order to be neglected during making the chart and the chart was created automatically with points connected with line.

right now when i start using Excel 2016, i lost this option and all charts created by my sheet is having a gabs in the charts at the places containing the #N/A.

i know there i way by Right click on the chart and choose Select Data, or choose Select Data from the ribbon. Click on Hidden and Empty Cells in the bottom left of the Select Data Source dialog that appears. The Hidden and Empty Cell Settings dialog appears.  and i select Connect Data Points with Line.

but the problem is my sheet is creating about 15 charts for every sets of values and to do it manually for all them it will take a lot of time

is there is a way to do it automatically as i used to do in Excel  2013

0 Replies