If you have a large and complex table on a small monitor (for example, a laptop), then in the process of working with it, you often want to hide some of the currently unnecessary data (rows or columns). The main ways to solve this problem are row hiding and grouping. Here is the last method and will be discussed further.
Suppose we are working with such a complex multi-level data table:
Note that the table clearly shows nested levels of data. You can often see this structure in:
- budgeting – items of income / expenses are grouped into blocks (cost centers) or geographically (country-region-city)
- project management – project stages are usually broken down into smaller subtasks and activities
- construction estimates – in a similar way, calculations of the consumption of materials and their cost during construction are usually signed
- etc. – then think for yourself.
Doing such a grouping manually is very painful, due to the inconvenience and limitations of the grouping tools of Excel itself. Therefore, once faced with a similar task in one of my projects, I wrote a macro for automatically grouping such lists, which I want to share here. To apply it to your list, do the following:
Press the keyboard shortcut ALT + F11to open the Visual Basic Editor. In it, select the command from the menu Insert – Moduleto insert a new module and copy the macro text there:
Sub Multilevel_Group() Dim level As Single, i As Single Dim start As Single, LastRow As Single Const FIRST_ROW = 2 'list first row Const FIRST_COLUMN = 1 'list first column Const NUMBER_OF_LEVELS = 3 'number of levels Set ws = ActiveSheet ws.UsedRange .ClearOutline 'remove all groupings on the sheet LastRow = WorksheetFunction.Match("End", ws.Columns(FIRST_COLUMN), 0) 'determine the number of the last row 'go through the nested loop through the levels and group For level = 1 To NUMBER_OF_LEVELS start = 0 For i = FIRST_ROW To LastRow 'if we found the beginning of the group, remember the row number If ws.Cells(i, level+FIRST_COLUMN-1) <> "" And _ WorksheetFunction.CountA(ws.Cells(i + 1, FIRST_COLUMN).Resize (1, level)) = 0 Then start = i 'if we found the end of the group - group If WorksheetFunction.CountA(ws.Cells(i + 1, FIRST_COLUMN).Resize(1, level)) > 0 And start > 0 Then ws .Rows(start + 1 & ":" & i).Group start = 0 End If Next i Next level End Sub If necessary, the text can be slightly adjusted to suit your needs, namely, change:
- FIRST_ROW – the number of the first line of the list, starting from which the grouping will start. If your header is not one line or there is data above the table, change it.
- FIRST_COLUMN — number of the first column of the list, from which analysis and grouping begin. If there are more columns to the left of your table, then this constant needs to be changed as well.
- NUMBER_OF_LEVELS — number of levels (columns) for analysis. In the example above, we want to parse the first three columns, so the value of this constant is =3
Important! The macro assumes that:
- The levels are completed in order, i.e., for example, level 3 cannot be written unless it was preceded by level 2.
- In the first column of the list, the last line must contain the word the end, which is necessary for the macro to understand where the list ends and it’s time to stop:
To run the added macro for the list on the current sheet, press the keyboard shortcut ALT + F8, select our macro from the list Multilevel_Group And click the Run (run).
- What are macros, how to create them, where to copy macro text in Visual Basic
- Hiding/showing unnecessary rows and columns