On Fri, 1 Dec 2006 05:05:01 -0800, Dee wrote:

Need help with the following:
I have a given list of 5 digit random numbers starting with
00001 and ending with 99999
I need to be able to review my list example below:
10211
10219
11040
11089
and figure out from 00001 to 99999 what numbers are missing from the
beginning and end of the data stream. This seems easy - 00001 to 10210 -
then start up again at 11090 to 99999. The problem occurs when I try to pull
in all the missing data between 10211 and 10219. I need to be able to set a
criteria then extract all the data from a given set of numbers, which will
create a new list of numbers. This is not a process that can be done
manually due to the size of the main data source (50,000 lines of code)

Please help............

Brute force?

==========================
Option Explicit

Sub MissingNumbers()
Dim i As Long, j As Long
Const LowerLimit As Long = 1
Const UpperLimit As Long = 99999
Dim List As Range
Dim Output As Range

Set List = Range("A2:A100")
Set Output = Range("B1")

j = 0
For i = LowerLimit To UpperLimit
If Application.WorksheetFunction.CountIf(List, i) = 0 Then
j = j + 1
Output.Offset(j, 0).Value = i
End If
Next i

End Sub
=============================


--ron