Advertisement
remilian

group rows

May 16th, 2023 (edited)
79
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VBScript 1.20 KB | Source Code | 0 0
  1. Sub GroupRows()
  2.  
  3.     Dim ws As Worksheet
  4.     Dim lastRow As Long
  5.     Dim i As Long
  6.     Dim level As String
  7.     Dim prevLevel As String
  8.     Dim startRows As Object
  9.     Dim levelKey As Variant
  10.  
  11.     Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
  12.    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  13.  
  14.     ws.Outline.ShowLevels RowLevels:=1 ' Resets existing grouping
  15.    Application.ScreenUpdating = False
  16.  
  17.     Set startRows = CreateObject("Scripting.Dictionary")
  18.     startRows.Add Key:=ws.Cells(2, 1).Value, Item:=2
  19.  
  20.     prevLevel = ws.Cells(2, 1).Value
  21.  
  22.     For i = 3 To lastRow + 1
  23.         If i <= lastRow Then
  24.             level = ws.Cells(i, 1).Value
  25.         Else
  26.             level = ""
  27.         End If
  28.  
  29.         If level <> prevLevel Then
  30.             For Each levelKey In startRows.Keys
  31.                 If InStr(level & ".", levelKey & ".") <> 1 Then
  32.                     ws.Rows(startRows(levelKey) & ":" & i - 1).Group
  33.                     startRows.Remove levelKey
  34.                 End If
  35.             Next levelKey
  36.             startRows.Add Key:=level, Item:=i
  37.         End If
  38.  
  39.         prevLevel = level
  40.     Next i
  41.  
  42.     Application.ScreenUpdating = True
  43.  
  44. End Sub
  45.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement