Grouping Rows in VBA

Question

I have the code below that doesn't seem to be working. Essentially, rngList refers to a defined name range in Excel that is about 500 rows long and every n number of rows there is text (there are approximately 32 rows out of the 500 that have text). I am trying to go to the non-blank cells (by mimicking the ctrl + down command in Excel).

I am checking to see if they are blank, and if they are I want to group that cell. If it is not blank, I want to check the cell to the left and if it is 0, I also want to group it. The code I have now is essentially trying to do this but I am receiving the error below:

Group Method of Range Class Failed

It then goes on to highlight the following line:

Selection.Rows.Group

EDIT: Let's say instead of grouping rows that are blank, I want to group rows that have 1 in them. That way the crtl + down will actually go to that cell rather than the last row.

Thank you very much for the help!

The code is below:

rngList.Cells(1).Select
    i = 0

    Do While i < 32
        i = i + 1
        If Selection.Value = "" Then
            Selection.Rows.Group
        Else
            Selection.End(xlToLeft).Select
                If Selection.Value <> 0 Then
                    Selection.Rows.ClearOutline
                End If
        End If
        Selection.End(xlToRight).Select
        Selection.End(xlDown).Select

    Loop

Answer 1

Despite the age of this post, I thought I'd throw in my two cents for anyone who might stumble upon it. I hope I understand your question correctly. Here's what I've gathered:

Goal: For every row in the column of interest, group rows based on a criteria.

Criteria: The only rows in the group are those that either have no value (blank, null, empty) OR have a value AND have a neighboring cell (directly to the left) that has a value of 0. The only rows not in the group are those that are not blank and have a neighboring cell that is not 0.

Here is some sample data:

Note: the Range B1:B12 makeup the named range rngList, like the OP says they have.

Data Before Running Macro:

Data After Running Macro - Grouping Uncollapsed:

Data After Running Macro - Grouping Collapsed:

The code that handles this:

To make this code work: In the VBE (Visual Basic Editor), open the worksheet that contains the data to group (also contains the named range rngList) and paste this code, then run the macro.

Note: The comments are added to explain certain parts in further detail, though I believe the code itself is written in a way that can explain itself (e.g. variable names are meaningful and logic makes sense).

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
End Sub

Answer 2

I have used Sam's code to group without using column A. Thought others could find it useful.

Sub Group_Jobs()

Dim myRange As Range
Dim rowCount As Integer, currentRow As Integer
Dim firstBlankRow As Integer, lastBlankRow As Integer
Dim currentRowValue As String
Dim nextRowValue As String

Application.ScreenUpdating = False 'Stop screen updating while grouping

'select range based on given named range
Set myRange = Range("A1:A1000")
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
    nextRowValue = Cells(currentRow + 1, myRange.Column).Value

'Assign firstBlankRow & lastBlankRow
    'if currentRowValue = NotBlank(Job#) And nextRowValue = NotBlank(Job#) Then Skip
    'if currentRowValue = Blank          And nextRowValue = Blank          Then Skip
    'if currentRowValue = NotBlank(Job#) And nextRowValue = Blank          Then is firstBlankRow
    'if currentRowValue = Blank          And nextRowValue = NotBlank(Job#) Then is lastBlankRow
    If Not (currentRowValue = "" Or currentRowValue = "") Then
        If (IsEmpty(nextRowValue) Or nextRowValue = "") Then
            firstBlankRow = currentRow + 1
        End If
    ElseIf (IsEmpty(currentRowValue) Or currentRowValue = "") Then
        If Not (IsEmpty(nextRowValue) Or nextRowValue = "") Then
            If firstBlankRow <> 0 Then
                lastBlankRow = currentRow
            End If
        End If
    End If
    Debug.Print "Row " & currentRow; ": firstBlankRow: " & firstBlankRow; ", lastBlankRow: " & lastBlankRow

'Group firstBlankRow & lastBlankRow
    'if first & last blank rows have been assigned, create a group
    If firstBlankRow <> 0 And lastBlankRow <> 0 Then
        'Debug.Print "Row: " & currentRow; ", Outline Level: " & ActiveSheet.Rows(currentRow).OutlineLevel
        If Not ActiveSheet.Rows(currentRow).OutlineLevel > 1 Then 'Ignore if last row is already grouped
            Range(Cells(firstBlankRow, myRange.Column), Cells(lastBlankRow, myRange.Column)).EntireRow.Select
            Selection.Group
        End If
        firstBlankRow = 0: lastBlankRow = 0 'reset the first/lastBlankRow values to 0
    End If
Next

Application.ScreenUpdating = True 'Start screen updating as macro is complete
End Sub