I need to dynamically group rows together in an EXCEL worksheet. The obvious choice to do this will be VBA. I hope you can help.

My spreadsheet has 18 columns and (up to) 5,000 rows of data. I've written the following code which works "OK" however it fails to group the last group and those few rows afterwards.

Private Sub Worksheet_Activate()
    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("B16:B5000")
    rowCount = Cells(Rows.Count, myRange.Column).End(xlUp).Row

    firstBlankRow = 0
    lastBlankRow = 0
    'for every row in the range
    For currentRow = 16 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

To further clarify, to find the starting row to group, these conditions will have to exist:
Cell A(?) will be blank
Cell B(?) must not be blank

The end of the group will the next row that meets these above conditions minus one row - except where:

The last row will be - the first row where
Cell A(?) is blank or 0
Cell B(?) is blank or 0
Cell C(?) is blank or 0

minus 1 row.

I suspect this is where my problem is. I'm thinking that, because there won't be a last row that matches the criteria of the starting row, it's not going to group that last group.

Given my limited knowledge of VBA, does anyone have any thoughts on how to fix this issue?

In advance, thanks for the help.

cgeier,

Forgive me for asking the above question. For what it's worth, I have some VB.net history here and didn't see a group for VBA users. Because of the similarities between VBA and VB.net, I thought that some of the more experienced programmers could offer some advice on how to get the routine to pick up that last group.

Don

You might have better luck in the "Visual Basic 4/5/6" forum. You may want to include the version of Excel that you are using. Some sample data is also beneficial.

Visual Basic for Applications

"Visual Basic for Applications (VBA) is an implementation of Microsoft's event-driven programming language Visual Basic 6..."

"...Compatibility ends with Visual Basic version 6; VBA is incompatible with Visual Basic .NET (VB.NET)..."

Edited 2 Years Ago by cgeier

This question has already been answered. Start a new discussion instead.