# 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

'
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

startRow = 2
intTemp = Range("c2").End(xlDown).Row

For x = 2 To intTemp
If Cells(x, 3) &LT;&GT; 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...