Excel Group with VBA

[Graxa]

I am trying to group levels into certain groups where I found and used this code developed by Luke Moraga:

>>> You've already four times noted <<<
>>> use code - tags <<<​

Sub BuildOutline()
Dim lastRow As Long
Dim grpRow(1 To 8) As Long
Dim i As Long, j As Long
Dim curLVL As Long

Application.ScreenUpdating = False
With ActiveSheet
    .Cells.EntireRow.ClearOutline
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    'Setup initial values
   For i = 1 To 8
        grpRow(i) = lastRow
    Next i

    For i = lastRow - 1 To 2 Step -1
        curLVL = lvlCount(.Cells(i, 1).Value)
        If curLVL > lvlCount(.Cells(i + 1, 1).Value) Then
            grpRow(curLVL) = i
        ElseIf curLVL < lvlCount(.Cells(i + 1, 1).Value) Then
            .Range(i + 1 & ":" & grpRow(curLVL + 1)).EntireRow.Group
            'If we go up a level, all previous lower levels reset
            For j = curLVL + 1 To 8
                grpRow(j) = i - 1
            Next j
        End If
    Next i
End With
Application.ScreenUpdating = True
End Sub

Function lvlCount(WBS As String) As Integer
lvlCount = Len(WBS) - Len(Replace(WBS, ".", "")) + 1
End Function

Function lvlCount(WBS As String) As Integer
lvlCount = Len(WBS) - Len(Replace(WBS, ".", "")) + 1
End Function

In order for me to use this code, I copied the sheet of Original data and renamed it as Processed data. In the Processed data sheet, I deleted rows 1 to 11, so that the current row 12 (level header) will be the first row, then I added new column A and assign my level 0 to 1, level 1 to 1.1, level 2 to 1.1.1, level 3 to 1.1.1.1, level 4 to 1.1.1.1.1, level 5 to 1.1.1.1.1.1, level 6 to 1.1.1.1.1.1.1, levels 7 onwards to 1.1.1.1.1.1.1.1 then I run the code which works as expected since I wanted the level 0 to be in group 1, level 1 in group 2, level 2 in group 3, level 3 in group 4 and so on.

Is there a means to enhance the code to remove the manual update like selecting specific row to start the grouping and using the values (actual number values like 0, 1, 2...) in the level column instead of assigning WBS numbering structure?

Lastly, if I wish to do this grouping repeatedly in different set of files, is there a way to skip the copying and pasting of this code to the new file, like is it possible to copy the new data from the new file to this file with the code and process the data here?

Thank you in advance and have a great day ahead.

 

[Marc L]

As anything is doable if only the Logic is respected !​

According to this forum rules when posting a VBA procedure you must use the Code option from the 3 dots (Insert) icon.​

You can use the Range.Find method to get the header data as where to start and to group the same method can be used as well …​

You can use Application.InputBox method to select the opened new file source range to copy to your VBA procedure workbook.​

​