CODEWITHSUNDEEP
excelvbagrouping
Chris Slabaugh
Chris Slabaugh

Reputation: 11

Excel vba - Group rows between blanks

Bear with me, first question I've asked here.

I'm trying to group rows into an outline between blank subtotal rows in a raw data pull as part of a larger formatting process.

Example data would look like (sorry, still new user, not allowed to just paste as far as I can figure.): Example Data Example Result

Been working off of the answers I found in Grouping Rows in VBA

Public Sub GroupCells()
Dim myRange As Range
Dim rowCount As Integer, currentRow As Integer
Dim firstBlankRow As Integer, lastBlankRow As Integer
Dim currentRowValue As String
Dim neighborColumnValue As String

'select range based on given named range
Set myRange = Range("rngList")
rowCount = Cells(Rows.Count, myRange.Column).End(xlUp).Row

firstBlankRow = 0
lastBlankRow = 0
'for every row in the range
For currentRow = 1 To rowCount
    currentRowValue = Cells(currentRow, myRange.Column).Value
    neighborColumnValue = Cells(currentRow, myRange.Column - 1).Value

    If (IsEmpty(currentRowValue) Or currentRowValue = "") Then
        'if cell is blank and firstBlankRow hasn't been assigned yet
        If firstBlankRow = 0 Then
            firstBlankRow = currentRow
        End If
    ElseIf Not (IsEmpty(currentRowValue) Or currentRowValue = "") Then
        'if the cell is not blank and its neighbor's (to the left) value is 0,
        'and firstBlankRow hasn't been assigned, then this is the firstBlankRow
        'to consider for grouping
        If neighborColumnValue = 0 And firstBlankRow = 0 Then
            firstBlankRow = currentRow
        ElseIf neighborColumnValue <> 0 And firstBlankRow <> 0 Then
            'if firstBlankRow is assigned and this row has a value with a neighbor
            'who isn't 0, then the cell one row above this one is to be considered
            'the lastBlankRow to include in the grouping
            lastBlankRow = currentRow - 1
        End If
    End If

    'if first AND last blank rows have been assigned, then create a group
    'then reset the first/lastBlankRow values to 0 and begin searching for next
    'grouping
    If firstBlankRow <> 0 And lastBlankRow <> 0 Then
        Range(Cells(firstBlankRow, myRange.Column), Cells(lastBlankRow, myRange.Column)).EntireRow.Select
        Selection.Group
        firstBlankRow = 0
        lastBlankRow = 0
    End If
Next

It's grouping the blank cells together, but I need to figure out how to make lastBlankRow pick up the next blank row after the next block of data.

Thanks in advance!

Upvotes: 1

Views: 1706

Answers (1)

Tate Garringer
Tate Garringer

Reputation: 1529

You could achieve this in much less code by using a Do While loop nested in your For-Next and keeping a count of non-blank cells in column B, grouping using that count - 1 and then adding that count to your iteration.

Tested with a recreation of your data structure and achieved desired results:

Sub GroupRows()

    Dim r As Long
    Dim i As Long
    Dim rowCount As Long

    rowCount = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To rowCount
        r = 0
        Do While Range("B" & i + r) <> "" And Not IsEmpty(Range("B" & i + r))
            r = r + 1
        Loop
        Range("B" & i & ":B" & i + r - 1).Rows.Group
        i = i + r
    Next i

End Sub

Upvotes: 2

Related Questions