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.
