Posts by rftech816

    I have a spreadsheet that has a column of numbers going from 700000 up to 799999. Idealy they should be consecutive but some numbers are missing.

    from a previous post: http://www.ozgrid.com/forum/showthread.php?t=55177&page=1
    I used its suggestion of:

    Sub test_v2()
    Dim i As Long, j As Long
    i = Cells(Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
    For j = i To 1 Step -1
    If Cells(j + 1, 1) <> "" Then
    If Cells(j + 1, 1).Value - Cells(j, 1).Value > 1 Then
    x = Cells(j + 1, 1).Value - Cells(j, 1).Value
    Rows(j + 1 & ":" & x + j - 1).Insert
    End If
    End If
    Next j
    With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=Row()"
    .Value = .Value
    End With
    Application.ScreenUpdating = True
    End Sub

    It work to an extent, for instance I have these numbers
    700231
    728731
    732684
    732685
    735123
    735124
    740202
    740213
    740214
    740216
    740217
    When I ran the above macro it started at 1 and only went up to 39987. I need it to start at 700000 and go up to whatever my last number is(not to be above 799999) and insert rows for the missing numbers. I am willing to break down my list of numbers to an amount of rows that excel is limited to. And does it matter which column has the numbers I am trying to make consecutive?