Displaying Nested Subtotals
Posted by CJ on June 05, 2001 2:01 PM
I have code that imports an access query into Excel and performs some calculations on it, then sorts it, formats it and adds subtotals to it. Is there a way to show the "tree" on the left side - with the plus signs to drop down details? Any help is appreciated.
Thanks,
CJ
Posted by Mark W. on June 05, 2001 2:18 PM
After choosing the Tools | Options... menu command
look to see if the View tab's "Outline symbols"
checkbox is checked. If not, check it and you
should see the outline. If it's already checked
then your data is no longer (if ever) outlined.
Posted by CJ on June 05, 2001 2:28 PM
The box is checked. I have code that adds the subtotals. It is shown below. Is there another way to calculate the subtotals and show that outline in code? Thanks for your quick response.
CJ
Sub Format_click()
Dim intTemp As Integer
Dim startRow As Integer
'
Cells.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
startRow = 2
intTemp = Range("c2").End(xlDown).Row
For x = 2 To intTemp
If Cells(x, 3) <> Cells(x + 1, 3) Then
Rows(x + 1 & ":" & x + 1).Insert Shift:=xlDown
Cells(x + 1, 4) = Cells(x, 4) & " Total"
Cells(x + 1, 4).Font.Bold = True
Cells(x + 1, 7) = "=SUBTOTAL(9,G" & startRow & ":G" & x & ")"
Cells(x + 1, 8) = "=SUBTOTAL(9,H" & startRow & ":H" & x & ")"
Cells(x + 1, 9) = "=SUBTOTAL(9,I" & startRow & ":I" & x & ")"
Cells(x + 1, 10) = "=SUBTOTAL(9,J" & startRow & ":J" & x & ")"
Cells(x + 1, 12) = "=SUBTOTAL(9,L" & startRow & ":L" & x & ")"
Worksheets("RTF").Columns("L").Hidden = True
Cells(x + 1, 13) = "=SUBTOTAL(9,M" & startRow & ":M" & x & ")"
Worksheets("RTF").Columns("M").Hidden = True
Cells(x + 1, 14) = "=SUBTOTAL(9,N" & startRow & ":N" & x & ")"
Cells(x + 1, 15) = "=SUBTOTAL(9,O" & startRow & ":O" & x & ")"
Rows(x + 1 & ":" & x + 1).Font.Bold = True
startRow = x + 2
x = x + 1
End If
Next
Range("H:J,L:O").NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
Columns("K:K").NumberFormat = "0.00%"
Range("A" & (lRow + 1)).Select
Range("A:Z").Columns.AutoFit
Range("A1").Select
End Sub
Posted by Mark W. on June 06, 2001 6:04 AM
CJ, as I see it you have 2 choices. You can
change the code below to take advantage of Excel's
Subtotal functionality (see the Data | Subtotals...
menu command) or have your code apply an appropriate
outline (see the Data | Group and Outline | Group...
menu command). One thing is for certain... your
code doesn't create an outline so there's none to
to show. I'd use the Macro Recorder to get a feel
for the VBA commands associated with both the
Subtotal and outlining menu commands mentioned above.
