For Loop, jumpoing back all of a sudden

In cells A2:A15 I have:

0801
0801
0801
0801
0803
0803
0804
0804
0805
0805
0807
0807
0809
0809

I am trying to insert a row wherever there is a blank. I am using the Step Into and the Local Window. On the 4th iteration, the code enters the IF statement. When I press F8 on the Set statement and the cursor moves to END IF, at this point, the value of "cell" is 0803 on row 7. This is what I am expecting to see at this point. Now press F8 two more times and the cursor lands on the IF statement. At this point however, the value of "cell" is empty on the 6th row.

If cell was on row 7, then the next iteration of the For loop occurs, how is it that cell is jumping back to row 6? I want it to stay on row 7.

 For Each cell In Range("A2:A15")
        If cell.Value <> cell.Offset(1, 0).Value Then
            cell.Offset(1, 0).EntireRow.Insert
            Set cell = cell.End(xlDown)
        End If
    Next cell

Originally Posted by jaslake

Hi Excel_vba

When adding or deleting rows it's better to iterate through the cells from the Bottom to the Top, like so

Option Explicit

Sub test()
    Dim lr As Long, i As Long
    Dim rng As Range
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("A1:A" & lr)
    With rng
        For i = lr To 2 Step -1
            If Not rng(i).Value = rng(i).Offset(-1, 0).Value Then
                rng(i).EntireRow.Insert
            End If
        Next i
    End With
End Sub

Hello Jaslake. I don't understand the purpose of "For i = lr To 2"

Hi,
Generally speaking, when you're inserting or deleting rows on a worksheet, it's a LOT easier to loop bottom to top. Otherwise you'll drive yourself crazy trying to keep track of the things to 'skip'.

Try this code:

Sub Better()
    Dim i As Long
    'inserting/deleting rows works a LOT simpler if you loop bottom to top..
    For i = 15 To 3 Step -1  'don't bother to check the top cell, unless you really want an extra row
        If Cells(i, 1) <> Cells(i - 1, 1) Then
            Worksheets("Sheet2").Cells(i, 1).EntireRow.Insert
        End If
    Next i
End Sub

To see what's going on in your code, add a couple of DEBUG.Print lines:

Sub Old()

    For Each cell In Range("A2:A15")
        Debug.Print cell.Address
        If cell.Value <> cell.Offset(1, 0).Value Then
            cell.Offset(1, 0).EntireRow.Insert
            Set cell = cell.End(xlDown)
            Debug.Print cell.Address
        End If
    Next cell
End Sub
You'll see that cell goes to a7 like you want, but the loop through the collection then goes to the next item on it's list, which is a6

Originally Posted by contracting

Hi,
Generally speaking, when you're inserting or deleting rows on a worksheet, it's a LOT easier to loop bottom to top. Otherwise you'll drive yourself crazy trying to keep track of the things to 'skip'.

Try this code:

Sub Better()
    Dim i As Long
    'inserting/deleting rows works a LOT simpler if you loop bottom to top..
    For i = 15 To 3 Step -1  'don't bother to check the top cell, unless you really want an extra row
        If Cells(i, 1) <> Cells(i - 1, 1) Then
            Worksheets("Sheet2").Cells(i, 1).EntireRow.Insert
        End If
    Next i
End Sub

To see what's going on in your code, add a couple of DEBUG.Print lines:

Sub Old()

    For Each cell In Range("A2:A15")
        Debug.Print cell.Address
        If cell.Value <> cell.Offset(1, 0).Value Then
            cell.Offset(1, 0).EntireRow.Insert
            Set cell = cell.End(xlDown)
            Debug.Print cell.Address
        End If
    Next cell
End Sub
You'll see that cell goes to a7 like you want, but the loop through the collection then goes to the next item on it's list, which is a6

Hello Contracting. Thank you for your reply. What is the purpose of "For i = 15 To 3 "