PDA

View Full Version : insert empty row where the content between two lines are different



sakura.saki
06-20-2012, 08:36 AM
Hi, now I have a list on sheet1 with not totally empty row and I have to do the following: I have to insert one entire empty row when the team name changes...and it should look like the one on tab "teams CW". I tried to write a piece of code(which is not in the attachment), but it only incerts a lot of rows under the first row.

so is there a method to do so, without the code to find a certain name of a team and insert one row above...because I have like 30 teams later, and it will be a lot of work.

thanks!

CatDaddy
06-20-2012, 10:53 AM
you have to do it starting from the last row going up instead
Sub insert_rows()

Dim lr As Long, i As Long
ActiveWorkbook.Sheets("teams CW").Activate
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = lr To 3 Step -1
If Cells(i, 3).Text <> Cells(i - 1, 3).Text Then
Cells(i, 1).EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Next i
End Sub

sakura.saki
06-21-2012, 12:54 AM
works perfectly, thank you so much:friends:

CatDaddy
06-21-2012, 08:47 AM
no problem