Inserting Rows Based on Change in Value in Column

[FrenchCelt]

Hello,

I'm coding a macro that will insert a row where the text changes from one value to another. Here is a example:

Name	Department	Shift
Myah	OF	10A
Kenigia	OF	10A
Netta	OF	10A
Kendall	OF	10A
Andrew	OF	10A
Donnie	OF	10B
Summer	OF	10B
Shanika	OF	10B
Anthony	OF	10B
Kyle	OF	12A
Dustin	OF	12A
Arielle	OF	12A
Tony	Rec	12B
Josh	LDPA	12B
Jacob	OF	12B
James	OF	12B

I want to insert a row where the Shift value switches from 10A to 10B, 10B to 12A, and 12A to 12B, thus creating separation between the shifts. This is how it should look afterward:

Name	Department	Shift
Myah	OF	10A
Kenigia	OF	10A
Netta	OF	10A
Kendall	OF	10A
Andrew	OF	10A
Donnie	OF	10B
Summer	OF	10B
Shanika	OF	10B
Anthony	OF	10B
Kyle	OF	12A
Dustin	OF	12A
Arielle	OF	12A
Tony	Rec	12B
Josh	LDPA	12B
Jacob	OF	12B
James	OF	12B

Does anyone have any suggestions?

 

[mumps]

Try:

Sub InsertRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = LastRow To 3 Step -1
        If Cells(x, 3) <> Cells(x - 1, 3) Then
            Rows(x).Insert
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[FrenchCelt]

This is what I got:

Name	Department	Shift
Myah	OF	10A
Kenigia	OF	10A
Netta	OF	10A
Kendall	OF	10A
Andrew	OF	10A
Donnie	OF	10B
Summer	OF	10B
Shanika	OF	10B
Anthony	OF	10B
Kyle	OF	12A
Dustin	OF	12A
Arielle	OF	12A
Tony	Rec	12B
Josh	LDPA	12B
Jacob	OF	12B
James	OF	12B

So it inserted rows between 12A and 12B, and then separated the different departments in 12B. And it inserted a row between the headers and the first row from 10A.

 

[mumps]

This is what I got:

Book3
	A	B	C
1	Name	Department	Shift
2	Myah	OF	10A
3	Kenigia	OF	10A
4	Netta	OF	10A
5	Kendall	OF	10A
6	Andrew	OF	10A
7
8	Donnie	OF	10B
9	Summer	OF	10B
10	Shanika	OF	10B
11	Anthony	OF	10B
12
13	Kyle	OF	12A
14	Dustin	OF	12A
15	Arielle	OF	12A
16
17	Tony	Rec	12B
18	Josh	LDPA	12B
19	Jacob	OF	12B
20	James	OF	12B
Sheet1

Are you using the macro on the data you posted?

 

[FrenchCelt]

Oops, I realized my mistake. I left out a column in my example, so I changed the code to make everything adjust the 4th column instead of the 3rd. Now it works perfectly. Thanks so much!

 

[mumps]

You are very welcome.