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

For starting at A12:

Sub SetOutline()

  Dim lStart As Long

  Dim lRow As Long

  With Range("A12").CurrentRegion 

    .ClearOutline

    lStart = 1

    For lRow = 1 To .Rows.Count

      If Not IsNumeric(Left(**.**Cells(lRow, 1), 3)) Then  ' note added . before Cells

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

        lStart = lRow + 1

      End If

    Next

  End With

End Sub

For columns

Sub SetColumnOutline()

  Dim lStart As Long

  Dim lColumn As Long

  With Range("A12").CurrentRegion ' this assumes row 11 is empty and column B is not empty

    .ClearOutline

    lStart = 3

    For lColumn = 3 To .Columns.Count

      If Not IsNumeric(Left(.Cells(1, lColumn), 3)) Then  ' note added . before Cells; column number is second argument

        if lColumn>lStart Then .Columns(lStart).Resize(,lColumn - lStart).EntireColumn.Group' note the comma after Resize(

        lStart = lColumn + 1

      End If

    Next

  End With

End Sub

---

---

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

Answer 3

One last question:

Is there a way to make the starting row more flexible?  IE, instead of starting my data at A1, starting at A12? etc?  I put an "X" in A1, A2, etc up until where my dataset starts (A13) and the rest of the code kicked in perfectly (So I wrote a macro that goes into A1:A12 and puts an X in there and then after the macro runs goes back into the A1:A12 range and deletes cell contents...clunky, i know, but it is all i could do with my limited skills.

Not a "need to have" but more of a nice to have.

Thanks

P.S.  Seperatley, I would like to expand this to have a columnar outline too.  It would look similar to this (starting in C12)

010-Completions   011-Attempts   012-Pct   013-Yards   014-Yards/Attp    PASSING    020-Rushes  021-Yards  023-Yards/Rush  RUSHING  etc....

C12                            D12              E12          F12              G12               H12               I12          J12                   K12               L20

I have started to try and figure it out by substituting Column for Rows....it doesn't quite work for me....Below is my code:

Sub Cols()

Dim lStart As Long

  Dim lColumns As Long

  Application.ScreenUpdating = False

  With Range("C12").CurrentRegion

   .ClearOutline

    lStart = 1

    For lColumns = 1 To .Columns.Count

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

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

       If lColumns > lStart Then .Columns(lStart).Resize(lColumns - lStart).EntireColumn.Group

        lStart = lColumns + 1

      End If

    Next

  End With

   Application.ScreenUpdating = True

End Sub

Answer 4

Hi Bill,

All of your changes worked great.  If row 11 isn't empty, how much more different would it be?  Also if I want to run these macrso consecutively, I am assuming that in one of them i have to comment out the .ClearOutline command, correct?  That is what I did and it worked. (otherwise it was wiping out the "other" outline and vice versa)

Finally, I was amazed at how some of these little subtle changes you made (comma here, period there) had such a profound effect.  I would like to understand this code better and what it is actually doing because I already have other ideas where I can apply it.  One thing I couldnt understand was what the variable lStart is, or does?  Is lColumn and lRow just a variable for Columns and Rows respectively?  I am also trying to understand the logic too and my best guess is that you are saying....Starting with a column, being counting (for lColumn = 3 to .Columns.Count)  If you dont see a number, in the cells, of the column's left three, then and this is the part/logic I dont understand too well 

if lColumn>lStart Then .Columns(lStart).Resize(**,**lColumn - lStart).EntireColumn.Group       

 lStart = lColumn + 1

Thanks again for all your help.  As you can see, I am not only interested in getting this to work but also understand what you did so that hopefully I can apply this to some other ideas i have for spreadsheets i maintain.

Thank you.

Answer 5

If row 11 was not empty then Range("A12").CurrentRegion would start below the first blank row above row 12 (or row 1 if none) so the code would be working on the wrong row.  I could improve the code so that was not an issue.

You are correct about having to remove the ClearOutline from one of the procedures if wanting to run both in sequence.

lStart is the column (or row) number of the start of the next group.

lColumn is the column number of the one we are currently looking at.

If it does not start with 3 numeric characters then it is the column after the end of the group.

So, if there are columns to be grouped, lColumn will exceed lStart

So, If lColumn>lStart Then we want to form a group starting with column lStart and ending at the column before lColumn

The number of columns in the group will be lColumn - lStart.

So

  .Columns(lStart)   is the lStart'th column of the region

  .Columns(lStart).Resize(,lColumn-lStart)  is that lStart'th column extened to the right to be the correct number of columns

  .Columns(lStart).Resize(,lColumn-lStart).EntireColumn   are the entire columns that are to be grouped (as I think you can only group  entire columns )

and 

  .Columns(lStart).Resize(,lColumn-lStart).EntireColumn.Group applies the Group method to those columns, forming them into a group.

lStart = lColumn + 1  is noting that the next group will start at the earliest after the non-numeric item we have just considered, in column lColumn

Hope this helps.

---

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