VBA to group rows that are between blank cells

[mstar0923]

Hi,​

Ive search and tried to modify several variations of vba code I was able to find on the internet but cant seem to get the macro to work the way I want it. I basically have several rows with data and beneath is a subtotal line in a different column. I basically just need the code to read column B and group every row that is between these blank rows. All help is greatly appreciated example below.

Thanks!

Here are the things I tried and didnt work. I dont know what I am doing wrong.

 

[mstar0923]

Here are the two things I tried:
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


Second one:
Sub GroupRows()
Dim Rng As Range
For Each Rng In Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
Rng.EntireRow.Group
Next Rng
End Sub


Here is what my data looks like

0
0
1	abc
1	abc
0