CODEWITHSUNDEEP
excelvba
Vinícius Affonso
Vinícius Affonso

Reputation: 85

Is there a way to Group Rows automatically?

I have an Excel like this:

Name       DOC        STATUS
John      ID         OK
John      ADRESS     Incomplete
John      SURNAME    Missing
Lara      ID         OK
Lara      ADRESS     Missing
Lara      SURNAME    Missing
Rony      ID         OK
Rony      ADRESS     OK
Rony      SURNAME    OK

The idea is, the names repeat itself 3 times because of each DOC. Is there a way to group rows automatically? Like, group every 3 rows?

Upvotes: 1

Views: 1655

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

If you will Group 3 rows, and since your data is in consecutive rows, you will get 1 huge range of rows grouped together.

If you want to group in a way, that only the first value is visible, and the other 2 are folded, then you actually need to Group only the 2nd and 3rd rows.

Code

Option Explicit

Sub GroupEveryNRows()

Dim i As Long, LastRow As Long

' modify "Sheet1" to your sheet's name
With Sheets("Sheet1")
    ' find last row with data in Column A
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    ' loop from 2nd row till last row with data (assuming the first row has headers)
    For i = 2 To LastRow Step 3
        Range("A" & i + 1 & ":A" & i + 2).Rows.Group
    Next i
End With

End Sub

Upvotes: 1

Related Questions