How to eliminate Grand Total from Subtotal function?

[Larry]

I know this can be done in a Pivot Table, but I am not using a Pivot. I have tried to code it out using a macro, but I have yet to be successful.

Any information will be greatly appreciated.

[dk]

Hi,

Unfortunately you can't remove the Grand Total when using subtotals either manually or through code. If you omit the SummaryAboveData argument of the Subtotal method it will just default to adding the grand total at the bottom. The one way around this is to use some custom subtotal macro which subtotals the data and adds the SUBTOTAL formula for each change in your data. If you want some sample code then post an example of your data.

Regards,
Dan

[Al Chara]

You can't do it within the subtotal function, but how about something like the following:

Set GrandTotal = Cells.Find("Grand Total", LookIn:=xlWhole)
GrandTotal.EntireRow.Clear

[Larry]

Thank your for the information. I have been banging my head against my desk for two days trying to do this. Below is some sample data. I am needing to include subtotals at each change in WMS Part#.

WMS
Part# AVL
54321 2
55432 1
56789 0

[ This Message was edited by: larry on 2002-04-05 13:16 ]

[Larry]

Al,

That worked to eliminate the GT row, but the range still exists. I am also needing to eliminate the range so I will still be able to dynamically set the print range.

Thanks for your help.

[Al Chara]

Not sure if I know what you mean by eliminating the range, but you can try and replace "clear" with "delete". That might do what you want to do.

[Larry]

By range, I am referring to the range at the left of the spreadsheet designated by the 1,2 and 3 buttons in the subtotal column. For some reason, when I refresh the data, the grand total row keeps being placed further and further down the spreadsheet even though the range of data is the same.



[ This Message was edited by: Larry on 2002-04-05 19:43 ]

[Anne Troy]

Don't know if I'm helping here or not. Have you thought about doing the subtotals, then selecting all cells (except grand) and hitting Edit-Go to-Visible cells only and copy/paste to another worksheet?

[Aladin Akyurek]

Larry,
Larry,

You seem to want just (sub)totals per WMS Part. It is not clear to me why you don't use Pivot Tables. What follows might also meet your need.

Consider the following sample in A1:B10 in a sheet called Data:

{"WMS Part#","AVL";
59238,2;
59238,1;
59239,3;
59239,2;
59241,1;
59241,3;
59242,2;
59242,1;
59244,2}

Activate the option Insert|Name|Define.
Enter NumDataRecs in the Names in Workbook box.
Enter in the Refers to box:

=MATCH(9.99999999999999E+307,Data!$B:$B)

Activate Add.

Enter WMSrange in the Names in Workbook box.
Enter in the Refers to box:

=OFFSET(Data!$A$2,0,0,NumDataRecs-1,1)

Activate Add.

Enter WMSrange in the Names in Workbook box.
Enter in the Refers to box:

=OFFSET(Data!$B$2,0,0,NumDataRecs-1,1)

Activate OK.

Note 1. The minus 1 in the NumDataRecs-1 bit refers to the fact that there is one row before the row where the actual data start.

Make a unique list of WMS Part#'s in A from A1 on (including the label 'WMS Part#') in a sheet called Summary. You can create such a list using Advanced Filter. In this sheet:

In B1 enter: SubTotals [ just a label ]

In B2 enter and copy down as far as needed:

=SUMIF(WMSrange,A2,AVLrange)

This is what you get in the results area in the Summary sheet:

{"WMS Part#","SubTotals";
59238,3;
59239,5;
59241,4;
59242,3;
59244,2}

For comparison: A Data|Subtotals approach would give you:

{"WMS Part#","AVL";
59238,2;
59238,1;
"59238 Total",3;
59239,3;
59239,2;
"59239 Total",5;
59241,1;
59241,3;
"59241 Total",4;
59242,2;
59242,1;
"59242 Total",3;
59244,2;
"59244 Total",2;
"Grand Total",17}

Note 2. The range names are so-called dynamic range names, allowing one to add records to and/or delete records from the data in the Data sheet without the need to adjust the SUMIF formula that we use here.

Aladin