Charting without Total Row and Blanks/ zero rows

msdfa123

New Member
Joined
Apr 12, 2017
Messages
1
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Fruit
[/TD]
[TD="align: center"]No# to Buy
[/TD]
[/TR]
[TR]
[TD]Pineapple
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Banana
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Strawberry
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]Cherries
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Orange
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Grapes
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD]31
[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]
Fruit
[/TD]
[TD]
No# to Buy
[/TD]
[/TR]
[TR]
[TD]Kiwi
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Peach
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top