Name the range in which you're searching as 'MyRange'
Use the following code (written extempore - so u may have to fine-tune it)
Sub FindMissing()
Dim rng as Range
Set rng=Range("MyRange"
FirstNum=rng.SpecialCells(xlCellTypeConstants, xlNumbers)
NumRow=WorksheetFunction.Match(FirstNum, rng, 0)
Num1=FirstNum-NumRow+1
Num2=Num1+Rng.Rows.Count-1
Msg=""
For i=Num1 to Num2
K=0
On error resume next
K=WorksheetFunction.Match(i,rng, 0)
If K=0 then msg=msg+cstr(i)+","
Next
If msg="" then
msg="no missing numbers"
Else
msg=msg+" are the missing nos "
End if
MsgBox (msg)
Thank you very much for the Macro. I feel very stupid for submitting the request -- for an apparently very simple problem. I have been struggling with it for about three days now and could come up with a solution. I have a very sophisticated program for modeling water pipes for flow and pressure calculation for water agencies. When assigning junctions the system, it cannot determine the next available junction number available. One has to extract the junc. no's, export them to excel, sort, and go look for missing numbers. You have saved me a lot of time.
No genuine problem should be considered stupid!
I've worked on such problems as yours - so feel free to ask if you have any specific technical queries... though this forum is the best place for spreadsheet related matter.