How to eliminate Grand Total from Subtotal function?

Larry

New Member
Joined
Apr 4, 2002
Messages
29
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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