Solved: Automate Grouping

[Imdabaum]

My coworker is looking for a way to automatically assign grouping to rows where the values of cellX = 5 or the last cell that surpasses 5. I'll try to demonstrate. I'm no excel wiz, and no math genius by any means either so I don't know why she needs it. Just understand how she wants it.

Cell1 Cell2
A 1.002
B 1.101
C 1.410
D 1.512
E 1.976
F 1.561
G 2.123
......

In this example we would assign a group to A and another group to E:G.
Any takers? I'll start working on it as well, but if anyone has ever already done something like that. I'm sure she would appreciate it.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim Lastrow As Long
Dim Startrow As Long
Dim i As Long
Dim tmp As Double

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Startrow = 2
For i = 1 To Lastrow

tmp = tmp + .Cells(i, "A").Value
If tmp > 5 Then

.Rows(Startrow).Resize(i - Startrow + 1).Group
Startrow = i + 2
tmp = 0
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[Imdabaum]

This works nice. However when I test it on her existing data, I end up with some unusual grouping.

Second sample data the grouping seems to be off
Cell1 Cell2
A 5.483786
B 2.301111
C 4.154707
D 5.090709
E 1.040552
F 1.92372
G 0.330972
H 4.86687
I 0.230499

Row A gets assigned group1, then C-D get grouped but B gets left out of the group. In the For loop, when I get to i=4 then the code breaks with "Application-defined or object-defined error, error#1004.

I thought I understood the process and tried to create an integer iCount to track how many rows it traveled before reaching 5 or exceeding it. I tried to use the iCount variable as the Startrow modifier instead of always adding 2. This didn't seem to get me anywhere though, so I obviously don't understand the process.

I've just tried to do this manually, and it appears that the grouping in Excel is a lot different than what I expected. There's no way to have BCD grouped if A is grouped. I might have to redefine the requirements.

[Bob Phillips]

How about this

[vba]

Public Sub ProcessData()
Dim Lastrow As Long
Dim Startrow As Long
Dim i As Long
Dim tmp As Double

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Startrow = 2
For i = 1 To Lastrow

tmp = tmp + .Cells(i, "A").Value
If tmp > 5 Then

If Startrow <= i And i - Startrow + 1 > 0 Then

.Rows(Startrow).Resize(i - Startrow + 1).Group
End If

Startrow = i + 2
tmp = 0
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

When you group, you have to leave one element of that set ungrouped, to contain the expand marker.

[Imdabaum]

Oh, that makes sense. I'm use to the first item being the expand button and all subsequent members hidden. I made a couple modifications and it works like a charm, thanks so much Xld. You're a genius.

[VBA]
Dim Lastrow As Long
Dim Startrow As Long
Dim i, iCount As Long
Dim tmp As Double

iCount = 1
Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Startrow = 2
For i = 1 To Lastrow

tmp = tmp + .Cells(i, "B").Value
If tmp > 5 Then
If iCount > 1 Then
If Startrow <= i And i - Startrow > 0 Then
.Rows(Startrow).Resize(i - Startrow).Group
End If
iCount = 1
End If
Startrow = i + iCount
tmp = 0
Else
iCount = iCount + 1
End If
Next i
End With

Application.ScreenUpdating = True
[/VBA]

[Imdabaum]

nevermind....