Charting without Total Row and Blanks/ zero rows

[msdfa123]

Hi everyone,

I have a macro that pulls data from SQL depending on certain inputs (date, location, etc.) The data yields fruit in the one column, and number to purchase in the second column, with a total row. (The SQL query is using 'rollup'). Below is an example:

Fruit	No# to Buy
Pineapple	2
Banana	1
Strawberry	7
Cherries	5
Apple	2
Orange	6
Grapes	8
Total	31

<tbody>
</tbody>

Now, if I run the query with different inputs, my result yield as per below, this time not as many rows have data, but still a total row.

Fruit​	No# to Buy​
Kiwi	6
Peach	3
Apple	2
Total	11

<tbody>
</tbody>

The macro is placing the original data from SQL on a sheet called "Summary" in a range "table1". I then have another sheet called "display" that references what is in "table1" on "Summary" sheets, hence the zero's as blank when there are less rows of data than can be... Sometimes there are 10 rows, sometimes there is only 1 etc.. But there is always a total row as well.

I have created a pie chart over the entire table range, so that when the query is run the pie chart automatically gets filled with the new data.

My question is, how do I set the chart to only take the rows WITH data.. excluding the total row (as this distorts the chart) and excluding blanks i.e. the rows with zeros.
I've tried using a pivot chart however this does not automatically update when the macro is run, importing new data.

I've been advised to use the 'address' and 'indirect' functions in excel but I am not entirely sure how this works.

Any suggestions are greatly appreciated!

Thanks!