Insert a blank row based on the value in a certain column

I am trying to find a macro or add-in that will insert a new row when it finds a change in value in a particular column.  In example below, I want to be able to select column A (Team) and have Excel insert a new row when it finds a new value. In this case I'd get a new row under Team and then under 0123.


TEAM      SCHOOL      STYLE    EMBELLISH       GARMENT COLOR      
0123      PURDUE      BAC           WDMK         BLKOGD                     
0123      PURDUE      BAC      WDMK              BLKOGD                     
0143      BAYLOR      BAC      WDMK        HNTGLD                     
0150      GEORGIA      BAC           WDMK          BLKRED
LVL 3
JeremywAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
wshark83Connect With a Mentor Commented:
try this:

Sub InsertLine()
    Dim i As Long
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If Cells(i - 1, 1) <> Cells(i, 1) Then
            Rows(i).Insert
        End If
    Next
End Sub
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
You can also try a non looping macro

Sub insertbwteams()
    Dim LR As Long
    LR = Range("A2").End(xlDown).Row
    Range("A1:A" & LR).AdvancedFilter Action:=xlFilterCopy, criteriarange:="", CopyToRange:=Range("A" & LR + 1), Unique:=True
    Rows(LR + 1 & ":" & LR + 1).Delete Shift:=xlUp
    Range("A2:E8").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("A2"), SortOn:=xlSortOnValues
    ActiveSheet.Sort.SetRange Range("A2:E" & Range("A2").End(xlDown).Row)
    ActiveSheet.Sort.Apply
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.