Finding Missing Numbers 2

[gbarritt]

I need to develope a VB Macro for Excel 97 to find missing
numbers from a column of consecutive numbers. Does anyone have a macro that would help.

 

[tigrek]

Missing numbers like empty cell
or like is there a 10 in this column
or which numbers from 1-100 are not in the column?

 

[Mala]

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&quot
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)

End sub

AbsNumRow=rng.Rows(NumRow).Row

 

[gbarritt]

Mala,

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.

Thanks Guy

 

[Mala]

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.