How to eliminate Grand Total from Subtotal function?
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: How to eliminate Grand Total from Subtotal function?

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Plano, Texas
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    Plano, Texas
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    Plano, Texas
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    Plano, Texas
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?
    ~Anne Troy

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com