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?