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.