Solved: Inserting Blank Row

[vzachin]

Hi,

I have data beginning in Cell B5 to B5000. I need to insert a blank row whenever each subsequent cell does not equal the previous cell.
i have something like this, but it's not working correctly if B5 & B6 is the same
how can i rewrite this code so that it would work properly? and can i make this code cleaner?


Sub SortMoreOc()
Sheets("test").Select
For i = 5 To ActiveSheet.Rows.Count
TermZ0 = Range("B" & i)
TermZ1 = Range("B" & i + 1)
If TermZ0 <> TermZ1 Then
With Range("B" & i)
.EntireRow.Insert
End With
If TermZ1 = "" Then
Exit Sub
End If
i = i + 1
End If
Next
End Sub



thanks
zach

[Norie]

Are you sure you are inserting in the correct row?

Range("B" & I+1).EntireRow.Insert

You also shouldn't be incrementing the loop counter yourself.

Try this.

Sub InsertRows()
Dim rng As Range
Set rng = Worksheets("Test").Range("B5")
While rng.Value <> ""
If rng.Value <> rng.Offset(1).Value Then
rng.Offset(1).EntireRow.Insert
Set rng = rng.Offset(1)
End If
Set rng = rng.Offset(1)
Wend
End Sub

[Bob Phillips]

Sub SortMoreOc()
Dim i As Long
Application.ScreenUpdating = False
With Worksheets("test")
For i = .Cells(.Rows.Count, "b").End(xlUp).Row To 5 Step -1
If .Cells(i, "B").Value <> .Cells(i + 1, "B").Value Then
.Rows(i).Insert
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

[vzachin]

thanks norie & xld

i'm still learning

zach