+ Reply to Thread
Results 1 to 7 of 7

Find missing numbers in a range

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    Find missing numbers in a range

    Hello everybody!

    Currently I use the following VBA code to find missing numbers in a range ("C2:C3000"), worksheet "2014".
    Sub MissingNumbers()Dim rng As Range
    Dim rng1 As Range
    Dim StartV As Single, EndV As Single, i As Single, j As Single
    Dim k() As Single
    Dim WS As Worksheet
    ReDim k(0)
    On Error Resume Next
    Set rng = Range("C2:C3000")
    StartV = InputBox("Start value:", , "4360201")
    EndV = InputBox("End value:", , "4360")
    On Error GoTo 0
    Set WS = Sheets.Add
    WS.Name = "MissingNumbers"
    WS.Range("A1:A" & rng.Rows.CountLarge).Value = rng.Value
    With WS.Sort
    .SortFields.Add Key:=WS.Range("A1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("A1:A" & rng.Rows.CountLarge)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Set rng1 = WS.Range("A1:A" & rng.Rows.CountLarge)
    For i = StartV To EndV
    On Error Resume Next
    j = Application.Match(i, rng1)
    If Err = 0 Then
    If rng1(j, 1) <> i Then
    k(UBound(k)) = i
    ReDim Preserve k(UBound(k) + 1)
    End If
    Else
    k(UBound(k)) = i
    ReDim Preserve k(UBound(k) + 1)
    End If
    On Error GoTo 0
    Next i
    ReDim Preserve k(UBound(k) - 1)
    WS.Range("B1:B" & UBound(k) + 1) = Application.Transpose(k)
    End Sub
    The missing numbers are listed in a new sheet called ("MissingNumbers"), in range ("B1:B").

    Please, could somebody help me with improving this code in order to:
    1. add exceptions that wouldn't be listed as missing numbers (e.g. 4360241, 4360256, etc).
    2. count the missing numbers at the end of the range ("B1:B") (in the first empty row, of course).
    3. copy the missing numbers (with the count number) in the range ("AJ2:AJ") of the worksheet ("2014").

    Thank you very much in advance!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,352

    Re: Find missing numbers in a range

    1) Place the list of exceptions into a specific range - say sheet "Exceptions" column A. Then change
    If rng1(j, 1) <> i Then
    k(UBound(k)) = i
    ReDim Preserve k(UBound(k) + 1)
    End If
    to
    If rng1(j, 1) <> i Then
        If IsError(Application.Match(i, Worksheets("Exceptions").Range("A:A"), False)) Then
            k(UBound(k)) = i
            ReDim Preserve k(UBound(k) + 1)
        End If
    End If
    2) Add this line before End Sub
    WS.Range("B" & UBound(k) + 1).Value = UBound(k)
    3) And, add this line before End Sub
    WS.Range("B1:B" & UBound(k) + 1).Copy Worksheets("2014").Range("AJ2")
    Last edited by Bernie Deitrick; 02-11-2014 at 02:59 PM.

  3. #3
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: Find missing numbers in a range

    Thank you so much for the prompt answer!

    Everything works perfect. You are a genius!
    Last edited by boboivan; 02-11-2014 at 03:57 PM.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,352

    Re: Find missing numbers in a range

    You modified the code incorrectly: I have changed the whole macro to reflect the changes, and made a few other changes. Let me know...

    Sub MissingNumbers2()
        Dim rng As Range
        Dim rng1 As Range
        Dim StartV As Single, EndV As Single, i As Single, j As Single
        Dim k() As Single
        Dim WS As Worksheet
        
        ReDim k(0)
        
        On Error Resume Next
        Set rng = Range("C2:C3000")
        StartV = InputBox("Start value:", , "4360201")
        EndV = InputBox("End value:", , "4360")
        On Error GoTo 0
        
        Set WS = Sheets.Add
        WS.Name = "MissingNumbers"
        WS.Range("A1:A" & rng.Rows.CountLarge).Value = rng.Value
        With WS.Sort
            .SortFields.Add Key:=WS.Range("A1"), _
                            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A1:A" & rng.Rows.CountLarge)
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Set rng1 = WS.Range("A1:A" & rng.Rows.CountLarge)
        For i = StartV To EndV
            If IsError(Application.Match(i, rng1, False)) Then
                If IsError(Application.Match(i, Worksheets("List").Range("A:A"), False)) Then
                     k(UBound(k)) = i
                    ReDim Preserve k(UBound(k) + 1)
                End If
            End If
        Next i
        WS.Range("B1:B" & UBound(k) + 1) = Application.Transpose(k)
        WS.Range("B" & UBound(k) + 1).Value = UBound(k)
        Worksheets("2014").Range("AJ2:AJ" & Rows.CountLarge).ClearContents
        WS.Range("B1:B" & UBound(k) + 1).Copy Worksheets("2014").Range("AJ2")
        
    End Sub
    Last edited by Bernie Deitrick; 02-11-2014 at 04:58 PM. Reason: edit

  5. #5
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: Find missing numbers in a range

    Yes sir, you're right!
    I missed to add a line in the code. I noticed that after I replied to you, and therefore I edited the reply .
    Regarding the new code, everything works great, except of refreshing range "AJ:AJ" in worksheet "2014" with the new values, every time I re-run the macro after making changes in my registrations or the list of exceptions.

    And now I noticed that is missing the line
    WS.Range("B1:B" & UBound(k) + 1).Copy Worksheets("2014").Range("AJ2")
    in the new code . So everything is perfect.

    Thank you again, you genius!
    Last edited by boboivan; 02-11-2014 at 04:37 PM.

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,352

    Re: Find missing numbers in a range

    I forgot to add this line to the second version:
    WS.Range("B1:B" & UBound(k) + 1).Copy Worksheets("2014").Range("AJ2")
    But it should be changed anyway, to clear out the old values
    Worksheets("2014").Range("AJ2:AJ" & Rows.CountLarge).ClearContents
    WS.Range("B1:B" & UBound(k) + 1).Copy Worksheets("2014").Range("AJ2")

  7. #7
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: Find missing numbers in a range

    Yeah, now is totally perfect!

    I was thinking last night if the code could be improved so as to display the missing numbers from range B:B, sheet ("2014") directly to range AJ:AJ, sheet ("2014"), without creating a new sheet ("MissingNumbers").

    Do you think is this possible? Thx again and sorry for the trouble.

    Meanwhile, I found a solution which works ok, but I don't think it's a very elegant one. What do you think of it?

    Sub MissingNumbers2()
        Dim rng As Range
        Dim rng1 As Range
        Dim StartV As Single, EndV As Single, i As Single, j As Single
        Dim k() As Single
        Dim WS As Worksheet
        
        ReDim k(0)
        
        On Error Resume Next
        Set rng = Range("C2:C3000")
        StartV = InputBox("Start value:", , "4360201")
        EndV = InputBox("End value:", , "4360")
        On Error GoTo 0
        
        Set WS = Sheets.Add
        WS.Name = "MissingNumbers"
        WS.Range("A1:A" & rng.Rows.CountLarge).Value = rng.Value
        With WS.Sort
            .SortFields.Add Key:=WS.Range("A1"), _
                            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A1:A" & rng.Rows.CountLarge)
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Set rng1 = WS.Range("A1:A" & rng.Rows.CountLarge)
        For i = StartV To EndV
            If IsError(Application.Match(i, rng1, False)) Then
                If IsError(Application.Match(i, Worksheets("List").Range("A:A"), False)) Then
                     k(UBound(k)) = i
                    ReDim Preserve k(UBound(k) + 1)
                End If
            End If
        Next i
        WS.Range("B1:B" & UBound(k) + 1) = Application.Transpose(k)
        WS.Range("B" & UBound(k) + 1).Value = UBound(k)
        Worksheets("2014").Range("AJ2:AJ" & Rows.CountLarge).ClearContents
        WS.Range("B1:B" & UBound(k) + 1).Copy Worksheets("2014").Range("AJ2")
        Application.DisplayAlerts = False
        WS.Delete
        
    End Sub
    Last edited by boboivan; 02-12-2014 at 07:16 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Can excel find the missing numbers for _+_=2 ?
    By JohnPaulino in forum Excel General
    Replies: 20
    Last Post: 04-14-2011, 03:16 PM
  2. Find Missing Numbers
    By kapeller in forum Excel General
    Replies: 16
    Last Post: 02-11-2011, 10:09 PM
  3. Find Missing Numbers
    By portal in forum Excel General
    Replies: 1
    Last Post: 04-05-2008, 09:09 AM
  4. Find missing sequential numbers
    By DTTODGG in forum Excel - New Users/Basics
    Replies: 13
    Last Post: 03-09-2006, 09:00 PM
  5. to find missing serial numbers in randomly generated numbers
    By B.H. Hadi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-01-2005, 07:00 PM

Bookmarks

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