One of the things that used to drive me nuts about VBA was the error message "Object doesn't support this property or method". I had
just used that property or method right over here in this other part of my program!
My problem, as it turned out, was that I already know about programming so I thought I knew what a "method" is. I didn't, and it was causing me no end of frustration. After a few years of this, a new coworker talked me into writing my own object, and the light dawned: A method is not a subroutine.
If you don't write programs already, then maybe my prejudice isn't yours. But if it is, let me know and we'll talk about it. Meanwhile, let's identify those rows we're going to group together. Here's a bit of the code that worked for me. Note the explanatory comments:
Code:
' Let's call the sub-phases the "minor" groups, and call phases the
' "major" groups. Each group is a set of rows marked by the contents
' of column L: The minor group starts just after each "Sub-Phase"
' in column L and ends just before the next "Phase" or "Sub-Phase", or
' on row rz. So we have to spot those rows.
rz = Range("L1").End(xlDown).Row 'last row
Dim rStart, rEnd
rEnd = 1
Do
rStart = StartRow(rEnd, "Sub-Phase", rz)
If rStart = 0 Then Exit Do
rEnd = EndRow(rStart, rz)
Range(rStart & ":" & rEnd).Group
Loop
End Sub
' Find the next row with the specified Label, and return the row after it.
' That's the first row of the next group.
Function StartRow(After, Label, LastRow)
For jr = After + 1 To LastRow
vv = Range("L" & jr).Value
If vv = Label Then Exit For
Next jr
If jr > LastRow Then StartRow = 0 Else StartRow = jr + 1
End Function
' Find the next instance of "Phase" or "Sub-Phase" and return the row before
' it; that's the last row of the current group.
Function EndRow(After, LastRow)
For jr = After + 1 To LastRow
If Range("L" & jr).Value = "Phase" Then Exit For
If Range("L" & jr).Value = "Sub-Phase" Then Exit For
Next jr
EndRow = jr - 1
End Function
I wrote two "functions" to make this easier to look at. A function is subroutine that returns a value to the program, just like what you've already seen in Excel itself. But maybe you've never written one of your own, so let's start by describing them.
One of the functions, named StartRow, is expected to tell the main program which row starts the next group. To do that, it has to start at a given row and look downward until the next time it sees "Sub-Phase" (if you're doing minor groups) or "Phase" (if you're doing major groups). So StartRow expects you to tell it a starting row and the label it's looking for ("Phase" or "Sub-Phase"). It also needs to know which is the
last row, so it doesn't go on too long. In the function I named those three arguments "After", "Label" and "LastRow". Knowing those three things, it can start on the row after "After", and keep looking down the rows until either column L has the "Label" value in it, or we've passed LastRow. Once it's stopped looking, it checks the row number; if we're past LastRow then StartRow returns 0, but otherwise it returns the row number just
after the Label it found. The calling program thus asks for StartRow and gets back the first row after "Phase" or "Sub-Phase".
The other function, EndRow, does the other side of this; you feed it After and LastRow, and it looks for the next "Phase" or "Sub-Phase" or the end of the data. It returns the row just before that, which is the last row in the group.
The main routine starts by assuming the fictitious "previous" group ended in row 1. There is no previous group at the beginning, of course; we just have to tell the program something so it knows where to start. Then I start going around and round (that's the Do...Loop part), calling StartRow and EndRow and grouping the rows between them.
Now, I didn't finish this program; all it does is the minor groups. One thing at a time, you see; I'm hoping that you'll study this and see how it works—well, see
that it works, first, then see how—and then either you'll figure out how to do the major groups yourself, or you'll at least be better equipped to understand the next-stage explanation.