Checking Sequential numbering
 

This is something I can't quite figure out.
I have two columns that I need to check the left column to make sure that
all numbers exist. If all do not exist then either add blank cells or the
missing numbers.
EX: before
A B
1 70001 20
2 70002 30
3 70005 20
Ex: after
A B
1 70001 20
2 70002 30
3 ; or 70003
4 ; or 70004
5 70005 20

Is this even possible?
I would be most appreciative for your help.
Any example would be helpful.
--
Bob

Checking Sequential numbering
 

Try this

Sub Test()
For N = Cells(65536, 1).End(xlUp).Row To 2 Step -1
If Cells(N, 1) - 1 < Cells(N - 1, 1) Then
Rows(N).Insert
Cells(N, 1) = Cells(N + 1, 1) - 1
N = N + 1
End If
Next N
End Sub

The code starts at the bottom and works upwards and should deal with
gaps of one or more than one number.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=548335

Checking Sequential numbering
 

Bob

one way:

Sub InsertRows()

Dim LastRow As Long
Dim i As Long
Dim RowDifference As Long

LastRow = Range("A65536").End(xlUp).Row

For i = LastRow To 2 Step -1
RowDifference = Range("A" & i).Value - Range("A" & i - 1).Value
If RowDifference 1 Then
Range("A" & i & ":A" & i + RowDifference - 2).EntireRow.Insert
End If
Next 'i

End Sub

Regards

Trevor


"Bob" wrote in message
...
This is something I can't quite figure out.
I have two columns that I need to check the left column to make sure that
all numbers exist. If all do not exist then either add blank cells or the
missing numbers.
EX: before
A B
1 70001 20
2 70002 30
3 70005 20
Ex: after
A B
1 70001 20
2 70002 30
3 ; or 70003
4 ; or 70004
5 70005 20

Is this even possible?
I would be most appreciative for your help.
Any example would be helpful.
--
Bob