Collapse item in subtotal by using VBA

[Guest]

I have made a macro which make a subtotal of a page in two levels:

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 10), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=False
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(8, 10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=False

Now I want to make a checkbutton where the user can select to collapse an
item on the second level. My problem is now that I can't find any information
on how to select a special item and then collapse it with VBA. When I record
the acction, where I manually click on the "-" to the left of this item and
it collapse. When I stop the recording of the macro I find no code.

Hope someone can help on this one.

 

[Bob Phillips]

Take a look at the outline object, for example

ActiveSheet.Outline.ShowLevels 3

 

[Guest]

This does not work as I want. I have a list and the

ActiveSheet.Outline.ShowLevels 2

collapse all on the 2 level - I just want to collapse one of the subtotals
on the 2 level.

2006
January
XXX 1000
YYY 2000
February
XXX 1025
YYY 500
March
XXX 200
YYY 562

By the Outline above all months are collapsed.

I want to collapse only the month February. Is this possible?

Karin

 

[Bob Phillips]

Karin,

The only thing I can think of is somehow identifying all of the rows within
the group and hiding them one by one. Here is an example of what I mean

Dim rng As Range
Dim i As Long

For i = 1 To Cells(Rows.Count, "A").End(xlUp).row
If Cells(i, "A").Value = "Feb" Then
Rows(i).ShowDetail = False
End If
Next i

 

[Guest]

Bob,

That works.

Thanks for your help,
Karin

Karin,

The only thing I can think of is somehow identifying all of the rows within
the group and hiding them one by one. Here is an example of what I mean

Dim rng As Range
Dim i As Long

For i = 1 To Cells(Rows.Count, "A").End(xlUp).row
If Cells(i, "A").Value = "Feb" Then
Rows(i).ShowDetail = False
End If
Next i