Grouping rows based on value

Question

I have done a bunch of searching and unable to find/figure out how to automatically group a Bill of Materials that I have. It is almost 70K lines of data and it is updated every month or so and isn't grouped. Without it grouped it is very hard to navigate. It does have levels of indenture (1-12) listed in column A which is what I am using to group the hierarchy with the Summary row on top (see example) BOM Grouping. Excel can only do eight levels of grouping but most of the part breakdowns fall in levels 4-10. "Auto Outline" did not work. I tried recording a macro of the manual way I have been doing it but didn't get very far. I am very new to VBA and Macros let alone coding and I really have no clue what to search for or where to start so any help would be greatly appreciated. Thank you in advance.

Answer 1

Sorry to @PeterT and @dbmitch for not responding sooner. Luckily the file that we get delivered already has the macro created but not ran. I wanted to share the code in case somebody else is wanting to do the same thing.

Sub User_Create_Collapsible_Tree()
'    UseSheet = "Output"
'    Sheets(UseSheet).Select
'
'    ' Create groups for collapsing tree
'    With Sheets(UseSheet)
 With Sheets(ActiveSheet.Name)

 Range("A3").Select

    ' Can only group max of 8 levels, so start grouping everything above 8
    For counter = 11 To 3 Step -1
            
        Range("A3").Select
        
        TOP_ROW = 0
        BOTTOM_ROW = 0
        
        R = 3
        Do Until .Cells(R, 1) = ""
        
            If .Cells(R, 1) > counter Then
            
                If TOP_ROW = 0 Then
                
'                        TOP_ROW = .Cells(R, 1)
                    TOP_ROW = R
                    
                End If
                
'                    BOTTOM_ROW = .Cells(R, 1)
                BOTTOM_ROW = R
                
            Else
            
                If TOP_ROW <> 0 Then
            
                    'HOLD_CELL = ActiveCell.Address
'                        Range(Rows(TOP_ROW), Rows(BOTTOM_ROW)).Select
'                        Selection.Columns.Group
                    TOP_TO_BOTTOM = TOP_ROW & ":" & BOTTOM_ROW
                    Rows(TOP_TO_BOTTOM).Group
                    
                    TOP_ROW = 0
                    'Range(HOLD_CELL).Select
                    
                End If
                
            End If
            
            R = R + 1
        
        Loop
        
        If TOP_ROW <> 0 Then
            
'                Range(Rows(TOP_ROW), Rows(BOTTOM_ROW)).Select
'                Selection.Columns.Group
            TOP_TO_BOTTOM = TOP_ROW & ":" & BOTTOM_ROW
            Rows(TOP_TO_BOTTOM).Group
            
        End If
    Next
End With

Range("A3").Select

End Sub

This returns results like this Grouping. The only thing you'll need to change is the Level range. Since excel can only do eight levels, I changed it from "8 to 1" to "11 to 3" and it worked beautifully.