Re: Identify missing record numbers

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Gord Dibben (gorddibbATshawDOTca)
Date: 01/04/05 

Date: Tue, 04 Jan 2005 12:39:08 -0800

kabobot

I found this code somewhere out there in code-land.

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

Lists the missing numbers from column A into Column C

Gord Dibben Excel MVP

On Tue, 4 Jan 2005 12:01:07 -0800, "kabobot"
<kabobot@discussions.microsoft.com> wrote:

>Hello.
>
>I have a spreadsheet that is used to track unique tracking codes assigned to
>companies. I am looking for a way to easily identify codes (within a range
>of 0000-8000) that has not been assigned. I would greatly appreciate help.
>
>Thank you!

Relevant Pages