Automate Outline/Grouping in Excel (macro)

Question

Hi,

I am looking for macro that will help me Auto Outline the rows in my spreadsheet. 

Here is a sample of the data I am working with:  Assume the data starts in Cell A2.

001-Ladanian Tomlinson

002-Mark Sanchez

New York Jets

010-Michael Vick

011-Desean Jackson

012-Brad Celek

Philadelphia Eagles

Basically I would want a macro that "reads" down and reads and groups all the data for a team once it hits the alphabetic "team" name.  My thinking is that if you can somehow read the first three characters and if it is a numeric value then includes those rows to outline until it hits a three character (alpha) combination. 

I hope this makes sense and is easy to do.  It will really save me a ton of time.

Thanks.

Answer 1

Is this close?

Sub SetOutline()

  Dim lStart As Long

  Dim lRow As Long

  With Range("A1").CurrentRegion

    .ClearOutline

    lStart = 1

    For lRow = 1 To .Rows.Count

      If Not IsNumeric(Left(Cells(lRow, 1), 3)) Then

        .Rows(lStart).Resize(lRow - lStart).EntireRow.Group

        lStart = lRow + 1

      End If

    Next

  End With

End Sub

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 2

Sorry - I tried editing previous reply after you read it.

If there are 2 adjacent rows with non-numeric starts it will try to group 0 rows.

Replace

.Rows(lStart).Resize(lRow - lStart).EntireRow.Group

with

if lRow>lStart Then .Rows(lStart).Resize(lRow - lStart).EntireRow.Group

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 3

Hi Bill,

I got it to work now....It looks like I had a title in A1 which caused it to break on me. 

I have one other little quirk where it stops on me.  (My fault since I didn't see this case in the beginning)....is there a way for the macro to skip over multiple "teams" with no players?  For Example:

001-Ladanian Tomlinson

002-Mark Sanchez

New York Jets

010-Michael Vick

011-Desean Jackson

012-Brad Celek

Philadelphia EaglesNew York Giants015-Tom Brady

New England Patriots

So in the above, the outlining stops after the Eagles b/c I think it thinks it reaches the end of the data???  There can be 3 or 4 teams (or more) that have no players and then resuming the numbering team sequence. In those case it can just hopefully skips over and resume the outlining on the next sequence...hope this makes sense.

Thanks again,

Answer 4

See previous reply which i think should deal with this issue

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 5

Thanks Bill.

It worked and works great now.

Much appreciated.