How to automatically nest rows of an Excel spreadsheet using level values

0 votes

In order to make an Excel 2016 spreadsheet easier to view and understand, I'm trying to apply grouping to it. The spreadsheet's data is organized in a manner resembling the one below:

    A  B  C

1   1  x  y

2   1  x  z

3   2  y  y

4   2  x  z

5   2  z  x

6   1  x  y

Rows 3, 4, and 5 are "children" of row 2, so they should be grouped together in accordance with the nesting levels I want in my spreadsheet, which are already represented by the numbers in Column A. In this particular spreadsheet, level 5 is the highest level attained. The spreadsheet does not require any additional interaction between the rows, such as computing subtotals. A decent solution will be applied elsewhere, and since the spreadsheet has about 800 lines, performing this task manually is not the best option.  How can I get the group function in Excel 2016 to recognize Column A as my grouping and apply the outline accordingly?

Nov 21, 2022 in Others by Kithuzzz
• 38,010 points
553 views

1 answer to this question.

0 votes

More grouping levels have been added to this VBA script.

It will group the rows according to the increment number as you have specified, starting with the row above.

Comments in the script that describe how it operates and potential failure scenarios are provided.

Just to be clear, if column A contains anything other than a number and if it does not match the requirements listed in the example remarks, it will fail.

Sub GroupRanges()

' Group levels must start at one and increase by one for each group level
' An error is produced if any levels are skipped
' Excel can only handle eight groups, the script will give a message and end if there are more than eight level groups
' Example: 1 1 2 3 3 4 4 5 will work
' Example: 1 1 2 2 2 4 4 5 will fail and produce an error, in this case group level 3 was skipped.
' Example: 1 2 3 4 5 6 7 8 9 Will fail, too many levels (more than 8)

Dim Sht As Worksheet
Dim LastRow As Long
Dim CurRow As Long
Dim StartRng As Integer
Dim EndRng As Integer
Dim GrpLvl As Integer
Dim MaxLvl As Integer

' This can be changed to define a sheet name
Set Sht = ActiveSheet

' find the highest number in the range to set as a group level
MaxLvl = WorksheetFunction.Max(Range("A:A"))

' If the Max level is greater than 8, then end the script as grouping cannot go beyond 8 levels
If MaxLvl >= 9 Then
MsgBox "You have " & MaxLvl & " group levels, Excel can only handle up to eight groups. This script will now end."
Exit Sub ' end the script if above eight groups
End If

'Set the Starting Group Level.
GrpLvl = 2

' find the last used row
LastRow = Sht.Cells(Sht.Rows.Count, "A").End(xlUp).Row
         
' Change the grouping to the cell above the range
Sht.Outline.SummaryRow = xlAbove

' Remove existing groups to prevent unrequired group levels.
' We now need to suppress error massages when trying to remove group levels that may not exist.
On Error Resume Next ' disable error messages

For x = 1 To 10   ' Repeat 10 times
    Sht.Rows.Ungroup   ' Remove Groups
Next x

On Error GoTo 0 ' Now it is important re-enable error messages

' Start the first loop to go through for each group level
For y = 2 To MaxLvl

    'Reset the variables for each group level pass
    CurRow = 1
    StartRng = 0
    EndRng = 0

    ' Start the inner loop through each row
    For Z = 1 To LastRow

        ' Check value of cell, if value is 1 less than current group level then clear the Start/End Range Values
        If Sht.Range("A" & CurRow) = GrpLvl - 1 Then
            StartRng = 0
            EndRng = 0
        End If

        ' If cell value equals the group level then set Range Values accordingly
        If Sht.Range("A" & CurRow) >= GrpLvl Then
        
            ' Check if row is the first of the range
            If Sht.Range("A" & CurRow - 1) = GrpLvl - 1 Then
                StartRng = CurRow
            End If
        
            ' Check if row is the Last of the range
            If Sht.Range("A" & CurRow + 1) <= 1 Then
                EndRng = CurRow
            End If
        
            ' If both range values are greater than 0 then group the range
            If StartRng > 0 And EndRng > 0 Then
                Sht.Rows(StartRng & ":" & EndRng).Rows.Group
            End If
    
        End If
    
        CurRow = CurRow + 1 ' increase for the next row

    Next Z  ' repeat the inner loop

    ' Increase to the next group Level
    GrpLvl = GrpLvl + 1

Next y ' repeat the first loop

End Sub

I hope this helps you.

answered Nov 21, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

How to trick an Excel function that wants a column as input to accept a list of values as if these were in a column

Use VSTACK: vstack to make an array: Use it as value ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,720 points
356 views
0 votes
1 answer

How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
388 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
818 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,720 points
1,072 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
877 views
0 votes
1 answer

Group by Sum in Excel

It is very easy and you can ...READ MORE

answered Feb 21, 2022 in Database by gaurav
• 23,260 points
481 views
0 votes
1 answer

INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX($I$2:$I$26,MATCH(V4,$R$2:$R$26,0))) The calculation ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 63,720 points
744 views
0 votes
1 answer

How to "Print" Excel Sheet on Custom Page Size like Din A1?

Try turning the pdf file into an ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,720 points
1,908 views
0 votes
1 answer

How to hide and unhide the columns of an excel sheet using asp.net

Use this. For Row: worksheet_sub.Row(i).Height = 0; For Column: ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 63,720 points
1,450 views
0 votes
1 answer

How to multi level sort an array in excel, using formulas? I am aware about the way, using SORT button on DATA tab

Use SORTBY, e.g. =SORTBY(A2:B5,A2:A5,1,B2:B5,1) Or simply&nb ...READ MORE

answered Jan 22, 2023 in Others by narikkadan
• 63,720 points
368 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP