CODEWITHSUNDEEP
excelvba
Tefalpan
Tefalpan

Reputation: 94

Excel checking if row is part of a group

I'm working on a macro to create some order in a very large excel doc. After a few operations on a specific selection, I would like to check if there are rows in that selected range that are grouped, and if so, they need to be ungrouped. After ungrouping all the rows (could be some seperate groups in the range, could be one big group or not even grouped at all), they need to be grouped all together. The part of the code below starts with selecting the specific range, I think the solution is looping through the range and checking all the rows if they are part of a group, and if so, they would be needed to be ungrouped. But Don't know how to translate this concept into a working code :) Also, an example of a range before the ungrouping/grouping operation is included below.

Range(Cells(Rstart, "H"), Cells(Rend, "H")).Select
Selection.Rows.Ungroup
Selection.Rows.Group

enter image description here

Upvotes: 0

Views: 8109

Answers (1)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15621

It is not quite clear what you mean to do. At any rate, I guess this will help you.

  1. Selecting complete rows.

    Dim rng As Range
Set rng = Selection
rng.EntireRow.Select

  2. Ungrouping (e.g., here). Maybe you can use the Outlinelevel to determine whether a row is grouped. This will ungroup rows in the first 20.

    Sub x()
    Dim lngRow As Long
    For lngRow = 1 To 20
        If ActiveSheet.Rows(lngRow).OutlineLevel > 1 Then
            Do While ActiveSheet.Rows(lngRow).OutlineLevel > 1
                ActiveSheet.Rows(lngRow).Ungroup
            Loop
        End If
    Next
End Sub

  3. Grouping. You already know how to do this.

Upvotes: 3

Related Questions