excelvbagroupingoutline

Collapse Some Groups (Not All) Using VBA

I'm hoping someone may be able to help get me here...

I have a huge list and am using grouping to organize it:

enter image description here

As you can see I have a Major Group row that has a number of products grouped into it. Then, some products have accessories that are then grouped under that part.

What I want, is to collapse ALL the accessories groups of ONLY the products showing. The below code will expand groups that are not expanded, I don't want that. 

shtOTP.Outline.ShowLevels RowLevels:=4
shtOTP.Outline.ShowLevels RowLevels:=3
shtOTP.Outline.ShowLevels RowLevels:=2
ActiveWindow.ScrollRow = shtOTP.Range("A3").row 'Go to top of sheet

I've tried looking into the .Outline.Parent .Outline.SummaryRow functions but as far as I can tell they don't identify a "parent" of the group.

I can do a loop like the following:

For i = 3 To getLastRow
    Dim nextRow As Integer
    nextRow = shtOTP.Rows(i+1).row
    If nextRow = ParentRow And i.EntireRow.Hidden = False Then
        'Collapse the group below the product
    End If
Next

But...

HOW do I identify if the nextRow is a parentRow?

HOW do I collapse JUST THAT GROUP?

Solution

  • I was able to come up with a short macro to do this:

    Application.ScreenUpdating = False

    Dim row As Range

    For i = 3 To getLastRow
        Set row = shtOTP.Range("A" & i)

        If row.EntireRow.Hidden = True Then                 
            'Do nothing
        ElseIf row.Value = "SM" Then                        'Minor Sub-model row, needs to be collapse
            If shtOTP.Rows(i).EntireRow.ShowDetail = True Then
                shtOTP.Rows(i).EntireRow.ShowDetail = False
            End If
        End If
    Next

Application.ScreenUpdating = True

    I placed "SM" in the A cell of the row for accessories to better identify them.