CODEWITHSUNDEEP
vbaexcel
Niko.K
Niko.K

Reputation: 307

Grouping in cells with VBA

I wnat to do some grouping in excel using VBA

My "criteria" column is "A" which is general a list of number that are obviously redundant and should be grouped to give the user a better understanding of the excel sheet

I have named column "A" "Vertrag__Nr."

My Code 

Sub Test()
Dim i As Integer, LastRow As Integer

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To LastRow

    If Not Left(Cells(i, 1), 2) = "Vertrag__Nr." Then
        Cells(i, 2).EntireRow.Group
    End If

Next i
End Sub

My problem is that my code instead of grouping by entries "Vertrag _Nr." (Column A) In groups the whole column into one big groupe

enter image description here

Upvotes: 2

Views: 120

Answers (1)

avb
avb

Reputation: 1753

as grouping is used for summaries, there has to be a place for summary between groups, they cannot be contiguous, try this code:

Sub Test()
Dim i As Integer, j As Integer, LastRow As Integer
Dim currVal As Variant
    With ActiveSheet
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        i = 2

        While i <= LastRow
            currVal = .Cells(i, 1).Value
            j = 0
            Do
                j = j + 1
            Loop Until .Cells(i + j, 1).Value <> currVal
            If j > 1 Then
                .Rows(i + j).Insert
                .Cells(i + j, 1).Value = currVal
                Range(.Cells(i, 1), .Cells(i + j - 1, 1)).EntireRow.Group
            End If
            i = i + j
        Wend
    End With
End Sub

Upvotes: 1

Related Questions