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

Thank you sir.

Worked Perefctly.

Answer 3

Hi Bill,

One quick question.  When I am running the code again, I received a "Application-defined or object defined error" 1004. and the yellow line highlights

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

Do you know what might be causing it to stop here?  Does it need to be fully qualified or is there a setting here in Excel that is triggering this.  It worked on my brother's computer last night but when trying it on my home pc it doesn't....

Any guesses?

Answer 4

Can you group the rows manually on your computer?

Is the worksheet protected?

Is the workbook shared?

If {Yes, No, No} then I'm stumped. 

But it would be good to know the values of lRow and lStart when it stops.

If lRow-lStart is 0 for some reason then it would fail.

This would correspond to 2 adjacent rows having non-numeric starts.

Otherwise you can probably see how to proceed. 

---

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

Answer 5

That makes two of us who are stumped.

I will keep working on it and see if I come up with anything.

Thanks.