VBA to remove legend with a blank cell from a chart

Hi, after hours and hours of searching for a solution to remove legend from a chart with a blank cell, I've come across few brilliant VBA solutions from some of the forums out there but somehow none of those scripts worked for my chart. I am using Excel 2016. 64 bits.

I am basically trying to create a dynamic stacked bar chart and then duplicate the chart (like to 100 of them) in the same worksheet with different data references.

The data table for those charts are being updated on a regular basis and I do not want the chart to show the legend with blank values or #N/A.

I have attached the sample file below.

Any help will be greatly appreciated.

Thanks.