1

I have a regular report delivered to me that contains grouped rows looking something like the below.

Grouped Rows

I would like to select/reference all of the rows within the group named 'Unassigned' (ie 'E', 'F', 'G') using VBA, so I can use the data elsewhere in my project. The problem is that the number of rows within this group and the position of the group itself within the worksheet can change; what will always remain the same is the name of the group ('Unassigned').

My assumption is that I cannot reference a particular group of rows in an outline like this due to the only information being the 'Outline Level', but hoping I am mistaken. Hope the issue is clear without the lack of code. Any help or alternative recommendations greatly appreciated.

Dratsy
  • 11
  • 1
  • Match the row with value 'Unassigned', from that row + 1 go one cell down at the time and check if corresponding EntireRow of the cell has OutlineLevel > 1 and keep going until that holds true. – milo5m Oct 14 '22 at 14:03

1 Answers1

0

You can use a helper sheet, Lets say the reference you shown is Sheet1 and the helper sheet is Sheet2. What I've did is I searched for the "Unassigned" and copy everything from that cell till the latest row to Sheet2 .

Sub Test()
    Dim Cell        As Range
    Dim i           As Long
    Dim j           As Long
    Dim x           As String
    Dim y           As String
    Dim z           As String
    
    Sheet1.Activate
    Sheet1.Columns("A:A").Select
    Set Cell = Selection.Find(What:="Unassigned", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    i = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    If Sheet2.Range("A1").Value = "" Then
        j = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
    Else
        j = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
    x = Cell.Address
    y = Replace(x, "$", "")
    z = Format(Replace(y, "A", ""), "0")
    Worksheets("Sheet1").Range("A" & z & ":B" & i).Copy Worksheets("Sheet2").Range("A" & j)
End Sub
Zen
  • 86
  • 7