+ Reply to Thread
Results 1 to 3 of 3

finding missing numbers

  1. #1
    Registered User
    Join Date

    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"

  2. #2
    Registered User
    Join Date

    little nudge

    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?

  3. #3
    Forum Contributor
    Join Date
    MS-Off Ver
    Excel 2007

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1