Excel VBA Grouping - HOWTO Multiple Groups in One Group Level

Question

Excel Sheet Layout: 10 tables of data stacked vertically, with one blank row inbetween each table.

Goal: Group & hide the blank data rows within each of the 10 tables (since not all rows in each table will return data).

Code: Tests each table individually, by row. Once it tests TRUE for a blank row, it exits the DO LOOP and hides the rest of that individual table from blank row to end row. It moves onto the next table and tests/hides the blank rows for this table... ETC. Until all tables have been tested and all blank rows are grouped/hidden.

Issue: I want all grouping to fall under one grouping level. So the "group levels" at the top left of the excel sheet will only show "1,2" as options and ALL groupings hide/close at the same time. After running this code, it shows 8 different group levels "1,2,3,4,5,6,7,8".

HOW CAN I GET MY CODE TO LAY ALL GROUPINGS IN ONE GROUP LEVEL?

'Group Attribute Rollups
x = linesheet_rollupatt11_row
Do Until x > linesheet_rollupatt125_row
    If Cells(x, linesheet_brand_clmn) = "" Then
        att1_count = x
        Exit Do
    End If
    x = x + 1
Loop

x = linesheet_rollupatt21_row
Do Until x > linesheet_rollupatt225_row
    If Cells(x, linesheet_brand_clmn) = "" Then
        att2_count = x
        Exit Do
    End If
    x = x + 1
Loop

x = linesheet_rollupatt31_row
Do Until x > linesheet_rollupatt325_row
    If Cells(x, linesheet_brand_clmn) = "" Then
        att3_count = x
        Exit Do
    End If
    x = x + 1
Loop

x = linesheet_rollupatt41_row
Do Until x > linesheet_rollupatt425_row
    If Cells(x, linesheet_brand_clmn) = "" Then
        att4_count = x
        Exit Do
    End If
    x = x + 1
Loop

x = linesheet_rollupatt51_row
Do Until x > linesheet_rollupatt525_row
    If Cells(x, linesheet_brand_clmn) = "" Then
        att5_count = x
        Exit Do
    End If
    x = x + 1
Loop

x = linesheet_rollupatt61_row
Do Until x > linesheet_rollupatt625_row
    If Cells(x, linesheet_brand_clmn) = "" Then
        att6_count = x
        Exit Do
    End If
    x = x + 1
Loop

x = linesheet_rollupatt71_row
Do Until x > linesheet_rollupatt725_row
    If Cells(x, linesheet_brand_clmn) = "" Then
        att7_count = x
        Exit Do
    End If
    x = x + 1
Loop

x = linesheet_rollupatt81_row
Do Until x > linesheet_rollupatt825_row
    If Cells(x, linesheet_brand_clmn) = "" Then
        att8_count = x
        Exit Do
    End If
    x = x + 1
Loop

x = linesheet_rollupatt91_row
Do Until x > linesheet_rollupatt925_row
    If Cells(x, linesheet_brand_clmn) = "" Then
        att9_count = x
        Exit Do
    End If
    x = x + 1
Loop

x = linesheet_rollupatt101_row
Do Until x > linesheet_rollupatt1025_row
    If Cells(x, linesheet_brand_clmn) = "" Then
        att10_count = x
        Exit Do
    End If
    x = x + 1
Loop

x = linesheet_rollupatt111_row
Do Until x > linesheet_rollupatt1125_row
    If Cells(x, linesheet_brand_clmn) = "" Then
        att11_count = x
        Exit Do
    End If
    x = x + 1
Loop

If Cells(linesheet_rollupatt1header_row, linesheet_vendorname_clmn).Value = "" Then
    Rows(linesheet_rollupatt1header_row & ":" & linesheet_rollupatt125_row).Select
    Selection.Rows.Group
    Else
    Rows(att1_count & ":" & linesheet_rollupatt125_row).Select
    Selection.Rows.Group
End If
If Cells(linesheet_rollupatt2header_row, linesheet_vendorname_clmn).Value = "" Then
    Rows(linesheet_rollupatt2header_row & ":" & linesheet_rollupatt225_row).Select
    Selection.Rows.Group
    Else
    Rows(att2_count & ":" & linesheet_rollupatt225_row).Select
    Selection.Rows.Group
End If
If Cells(linesheet_rollupatt3header_row, linesheet_vendorname_clmn).Value = "" Then
    Rows(linesheet_rollupatt3header_row & ":" & linesheet_rollupatt225_row).Select
    Selection.Rows.Group
    Else
    Rows(att3_count & ":" & linesheet_rollupatt325_row).Select
    Selection.Rows.Group
End If
If Cells(linesheet_rollupatt4header_row, linesheet_vendorname_clmn).Value = "" Then
    Rows(linesheet_rollupatt4header_row & ":" & linesheet_rollupatt225_row).Select
    Selection.Rows.Group
    Else
    Rows(att4_count & ":" & linesheet_rollupatt425_row).Select
    Selection.Rows.Group
End If
If Cells(linesheet_rollupatt5header_row, linesheet_vendorname_clmn).Value = "" Then
    Rows(linesheet_rollupatt5header_row & ":" & linesheet_rollupatt225_row).Select
    Selection.Rows.Group
    Else
    Rows(att5_count & ":" & linesheet_rollupatt525_row).Select
    Selection.Rows.Group
End If
If Cells(linesheet_rollupatt6header_row, linesheet_vendorname_clmn).Value = "" Then
    Rows(linesheet_rollupatt6header_row & ":" & linesheet_rollupatt225_row).Select
    Selection.Rows.Group
    Else
    Rows(att6_count & ":" & linesheet_rollupatt625_row).Select
    Selection.Rows.Group
End If
If Cells(linesheet_rollupatt7header_row, linesheet_vendorname_clmn).Value = "" Then
    Rows(linesheet_rollupatt7header_row & ":" & linesheet_rollupatt225_row).Select
    Selection.Rows.Group
    Else
    Rows(att7_count & ":" & linesheet_rollupatt725_row).Select
    Selection.Rows.Group
End If
If Cells(linesheet_rollupatt8header_row, linesheet_vendorname_clmn).Value = "" Then
    Rows(linesheet_rollupatt8header_row & ":" & linesheet_rollupatt225_row).Select
    Selection.Rows.Group
    Else
    Rows(att8_count & ":" & linesheet_rollupatt825_row).Select
    Selection.Rows.Group
End If
If Cells(linesheet_rollupatt9header_row, linesheet_vendorname_clmn).Value = "" Then
    Rows(linesheet_rollupatt9header_row & ":" & linesheet_rollupatt225_row).Select
    Selection.Rows.Group
    Else
    Rows(att9_count & ":" & linesheet_rollupatt925_row).Select
    Selection.Rows.Group
End If
If Cells(linesheet_rollupatt10header_row, linesheet_vendorname_clmn).Value = "" Then
    Rows(linesheet_rollupatt10header_row & ":" & linesheet_rollupatt225_row).Select
    Selection.Rows.Group
    Else
    Rows(att10_count & ":" & linesheet_rollupatt1025_row).Select
    Selection.Rows.Group
End If
If Cells(linesheet_rollupatt11header_row, linesheet_vendorname_clmn).Value = "" Then
    Rows(linesheet_rollupatt11header_row & ":" & linesheet_rollupatt225_row).Select
    Selection.Rows.Group
    Else
    Rows(att11_count & ":" & linesheet_rollupatt1125_row).Select
    Selection.Rows.Group
End If

Nick Peranzi Nick Peranzi · Accepted Answer · 2016-02-27T04:16:12

This is likely happening because your groupings overlap. Assuming all of your data is in one column and there is just one blank row between tables, you should either start the groupings one row lower or end them one row higher.

Also, consider avoiding selection (by .Select, then Selection.Rows.Group). See below code for examples.

To end the groupings one row higher, change the

Rows(linesheet_rollupatt1header_row & ":" & linesheet_rollupatt125_row).Select
Selection.Rows.Group

and similar lines to

Rows(linesheet_rollupatt1header_row & ":" & linesheet_rollupatt125_row - 1).Group

Alternatively, to begin the groupings row later, add a "+ 1" to each starting row, e.g.

Rows(linesheet_rollupatt1header_row + 1 & ":" & linesheet_rollupatt125_row).Group

or

Rows(att2_count + 1 & ":" & linesheet_rollupatt225_row).Group

Excel VBA Grouping - HOWTO Multiple Groups in One Group Level

1 Answers