Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subtotals Just Don't Add Up (XL97:SR2)

    The "Grand Total" line does not always appear when using VBA to generate subtotals based on a change of names and using the sum feature. Has anyone had this issue or have a workaround where the "Grand Total" line will always appear?

    Thanks,
    John

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Subtotals Just Don't Add Up (XL97:SR2)

    Can we see the code? Which Sum feature? If you re-run Data Subtotals without selecting the entire area including field headers and existing grand totals you can get anomalous results, but missing grand totals isn't one of them.

    Are you using something like this?

    ActiveCell.CurrentRegion.Subtotal GroupBy:=9, Function:=xlSum, _
    TotalList:=Array(13), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotals Just Don't Add Up (XL97:SR2)

    John,

    If I were to run the code twice the "Grand Total" line appears. One other thing....If I were to create the subtotal manually there may be instances where the "Grand Total" line does not appear. Here is a sample of the code I'm using:


    Sheets("" & oSheet & "_Detail").Select
    LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    Range("A1:G" & LastRow & "").Select
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("F2") _
    , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom
    Selection.SubTotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2

    Thanks,
    John

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Subtotals Just Don't Add Up (XL97:SR2)

    This is working for me on different sample data, I'm wondering if there's an issue in your data layout.

    Sub MyMacro()
    Dim LastRow As Long
    With Worksheets("Current")
    LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    With Range("A1:P" & LastRow & "")
    .Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("F1"), _
    Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom
    .Subtotal GroupBy:=6, Function:=xlCount, TotalList:=Array(6), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    End With
    .Outline.ShowLevels RowLevels:=2
    End With
    End Sub

    Can you attach an example of your WS?
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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