finding missing numbers

i have a list of 1-700 in column a. i wamnt to search column a and report which numbers are missing. ex...1,2,4 i want excel to automatically recognize that 3 is missing and tell me so i can create a "need this number list"

Sub DisplayMissing()
Dim C As Range, V As Variant
Dim prev&, k&, n&

k = 1
prev = 10000
For Each C In Intersect(Range("A:A"), ActiveSheet.UsedRange)
If C > prev + 1 Then
V = Evaluate("Row(" & prev + 1 & ":" & C - 1 & ")")
n = C - (prev + 1)
Cells(k, "C").Resize(n, 1) = V
k = k + n
End If
prev = C
Next C

End Sub

i found this...and it works as long as i input the range. problem is that the macro needs to be run on several sheets of different lengths.....how can i make the range dynamic?

also...if i want to export to another blank sheet......what section do i change...and how so?

Hi!

As I read it, the numbers in column A are consecutive except for a number of gaps.
And even if they aren't, a quick sort would do that bit.

If you put =IF(A2=A1+1,"","Gap") in B2 (not B1) and copy it down the 700 or so rows you have, the word "Gap" will appear where the numbers are not consecutive. It won't tell you how many are missing at that point, but it will tell you the place to look/sort out.

Alf