Help showing missing numbers in a sequence

[Cruiser69]

Hi all.

Could anyone help with showing missing numbers in a sequence

I have been using the code below which works well.

Sub Missing_Numbers_in_Sequence()


' This loops through Column A and checks for missing numbers in a sequence.
' Then shows the missing numbers in column C

   Dim iLoop As Long, iLoop2 As Long
Dim Last_Row As Long
Dim Old_Number As Long, New_Number As Long

Last_Row = Range("A5000").End(xlUp).Row
Old_Number = Val(Right(ActiveSheet.Range("A1").Value, 5))
For iLoop = 1 To Last_Row
    New_Number = Val(Right(Worksheets(1).Range("A" & iLoop).Value, 5))
    For iLoop2 = Old_Number To (New_Number - 2)
        ActiveSheet.Range("C" & Range("C5000").End(xlUp).Row + 1).Value = _
            "" & iLoop2 + 1 & ""
    Next iLoop2
    Old_Number = New_Number
Next iLoop


End Sub

The example below shows just 5 & 7 missing, which is correct

A	B	C
Number	Description	Missing Numbers
1	Chair	5
2	Speaker	7
3	Phone
4	TV
6	Table
8	Soundbar

If the number does not start at 1 as below, it shows all numbers from 1. Then shows the number that is really missing, which is 13

A	B	C
Number	Description	Missing Numbers
8	Chair	1
9	Speaker	2
10	Phone	3
11	TV	4
12	Table	5
14	Phone	6
		7
		13

Is there a way to just show the missing numbers from the first number entered


Thanks for looking,


Graham

 

[steve the fish]

See if changing this line:

ActiveSheet.Range("C" & Range("C5000").End(xlUp).Row + 1).Value = "" & iLoop2 + 1 & ""

to this:

If iLoop2 + 1 > Application.Min(Range("A1:A" & Last_Row)) Then
    ActiveSheet.Range("C" & Range("C5000").End(xlUp).Row + 1).Value = "" & iLoop2 + 1 & ""
End If

 

[Cruiser69]

Thanks Steve.
That is awesome.

 

[James006]

Hello,

Below is a macro for the missing values

Sub MissingValues()
Dim rng As Range, j As Variant
Dim StartV As Long, EndV As Long, i As Long
Dim k() As Long, Last_Row As Long

Last_Row = Range("A5000").End(xlUp).Row
ReDim k(0)

Set rng = Range("A2:A" & Last_Row)
StartV = Range("A2")
EndV = Range("A" & Last_Row)

    For i = StartV To EndV
        On Error Resume Next
        j = Application.Match(i, rng, 0)
        If IsError(j) Then
            k(UBound(k)) = i
            ReDim Preserve k(UBound(k) + 1)
        End If
    Next i
Range("C1") = "Missing values"
Range("C2:C" & UBound(k) + 1) = Application.Transpose(k)
End Sub

Hope this will help

 

[Peter_SSs]

You could also do it with a worksheet formula. This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Book1
	A	B	C
1	Number		Missing
2	8		13
3	9		15
4	10		16
5	11		17
6	12		19
7	14
8	18
9	20
10
Missing Numbers (2)

Cell Formulas
Range	Formula
C2:C9	C2	{=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDEX(A:A,A$2):INDEX(A:A,LOOKUP(9.99E+307,A:A))),A$2:A$100,0)),ROW(INDEX(A:A,A$2):INDEX(A:A,LOOKUP(9.99E+307,A:A))),""),ROWS(C$2:C2)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Cruiser69]

Thanks for all you replies

Graham

 

[Peter_SSs]

You're welcome. You have some choices.