MrExcel Publishing
Your One Stop for Excel Tips & Solutions

FIND GAP IN NUMBER SEQUENCE


Posted by ED on June 21, 2001 10:19 AM

HAVE LIST OF INVOICE NUMBERS (MOSTLY 4 OR 5 DIDGIT) AND NEED TO FIND THE MISSING ONES, HAVE THEM SORTED FROM LOW TO HIGH, BUT WOULD LIKE TO INSERT A BLANK ROW IF NEXT NUMBER IS NOT GREATER BY 1 , MANY OF THE NUMBERS ARE DUPLACATED SO IF NEXT NUMBER IS SAME OR NOT GREATER BY 1 , NO ROW INSERT IS NEEDED.


Posted by gregc on June 21, 2001 3:43 PM

Dim invoice1 As Integer
Dim invoice2 As Integer

While Len(ActiveCell) > 0
invoice1 = ActiveCell
ActiveCell.Offset(1, 0).Select
invoice2 = ActiveCell

If invoice2 - invoice1 > 1 Then
rownum = ActiveCell.Row
columnnum = ActiveCell.Column

Rows(rownum).Select
Selection.Insert shift:=xlDown
Cells(rownum + 1, columnnum).Select

End If
Wend