I have Charts w/ code to display only non-zero values where the data in an Excel template is populated by Access-to-Excel automation.

As an example, I'm interested in cells RCA!$C$67:$H$79. I can set that as the Chart data range, but can use the Legend Entries (Series) of Series1 and Series6 to display only non-zero entries as a dynamic range.

I have these Named Ranges which are used to edit series values ... Named Range
AAChartVals =OFFSET(RCA!$H$66,1,0,COUNTA(RCA!$H$67:$H$79),1) ... edit series values for Series1=KMI_Temp4.xlsm!ABChartLbls

Set ChartLabels like ... Named Range AAChartLbls =OFFSET(RCA!AAChartVals,0,-5)
... edit series values for Series6 =KMI_Temp4.xlsm!AAChartVals



The above works correctly as long as there is at least an entry in
RCA!$H$67:$H$79. However, no entry in RCA!$H$67:$H$79 makes an error message that there's an invalid formula.

My code will populate any first entry in cell $H$67. I can use an IF statement to see if $H$67 has an entry, and if so, will like to use VBA to set the edit series values to the above descriptions. However if $H$67 has no entry I'd like to set the Chart data range as cells RCA!$C$67:$H$79, which would display Series1 thru Series6 without edited series values.

Anyone with sample code? TIA, Bob