Inserting a blank row when number sequence changes

[Excel_vba]

In cells A2:A15 I have the following values:

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

When I encounter break in sequence I want to insert a blank row, e.g. between 0801/0803 and 0803/0804, etc. The program just hangs when I run it, but this I believe is tied to my problem. So I used the Step into along with the local window to see how the variable are changing with each step. The first time a break in sequence is detected, a blank row does get inserted. So far so good.

The problem is at this point, "cell" is now on the blank row. So a comparison to the cell below will always result in an inequality so then another row would get inserted. What I believe I need to do is to find a way to bump 'cell' down by one cell after a row gets inserted. Using the offset method results in a syntax error. I can't figure out what method I should use after this last offset statement. I don't need to select or copy or do anything really. All I need to do is find a way to tell Excel to shift 'cell' down by one cell. I think this should results in cell referring to the next number, for example, referring to 0803 after a blank row has been inserted above 0803.

Here is my code:

 For Each cell In Range("A2:A15")
        If cell.Value <> cell.Offset(1, 0).Value Then
            cell.Offset(1, 0).EntireRow.Insert
            cell.Offset(1,0)       -----> syntax error right here
        End If
    Next cell

Any help would be greatly appreciated.

[kalak]

it's usual, although it's not the only way, to loop upwards rather than downwards when deleting or inserting rows.
otherwise some rows may be "jumped"

As it stands the For each ... approach loops either downward or to the right, not left or upwards.

[JOHN H. DAVIS]

Does this help?

Sub Excel_vba()
Application.ScreenUpdating = False
Range("A" & Rows.count).End(3)(0).Select
Do Until ActiveCell.Row = 1
If ActiveCell.Value <> ActiveCell.Offset(1).Value Then
    ActiveCell.Offset(1).EntireRow.Insert
End If
    ActiveCell.Offset(-1).Select
Loop
Application.ScreenUpdating = True
End Sub

[Excel_vba]

Does this help?

Sub Excel_vba()
Application.ScreenUpdating = False
Range("A" & Rows.count).End(3)(0).Select
Do Until ActiveCell.Row = 1
If ActiveCell.Value <> ActiveCell.Offset(1).Value Then
    ActiveCell.Offset(1).EntireRow.Insert
End If
    ActiveCell.Offset(-1).Select
Loop
Application.ScreenUpdating = True
End Sub

Thank you for your reply. After the .end you have (3) and (0). What does the 3 and 0 refer to?

[JOHN H. DAVIS]

(3) is the same as (xlUp)
I actually never used (0) before. But I know it is an offset command.
(1) will select the last cell with data
(2) will select the next cell after the last cell with data. So I tested (0) and it selected the cell above the last cell with data, which is what I needed to do in your situation.

[kalak]

if lots of rows and you want it done faster, you might also like to try

Sub insertrows()

Dim r As Long, cl As Long, a, u(), i As Long
r = Cells(Rows.Count, 1).End(3).Row
cl = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
a = Cells(1).Resize(r + 1)
ReDim u(1 To 2 * r, 1 To 1)
For i = 1 To r
    u(i, 1) = i
    If a(i, 1) <> a(i + 1, 1) Then u(i + r, 1) = i
Next i
Cells(cl + 1).Resize(2 * r) = u
Cells(1).Resize(2 * r, cl + 1).Sort Cells(cl + 1), Header:=xlNo
Cells(cl + 1).Resize(2 * r).ClearContents

End Sub