Identify missing numbers in sequence

[Romulo Avila]

Good afternoon,
I need help, I have a column with about 1000 rows that contain values ​​in sequence (10,11,12, ...),
I needed a macro that checked in this column which numbers may be missing and which
these numbers were shown in a MsgBox.

Thanks

RomuloRDM

[offthelip]

This code will check the A column, adjust to suit:

Sub missingno()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))
msg = "Numbers missing are: "
For i = 1 To lastrow
   If inarr(i, 1) <> i Then
    msg = msg & i & " , "
   End If
Next i
MsgBox (msg)
End Sub

[Paul_Hossler]

Option Explicit


Sub MissingNumbers()
    Dim A As Variant
    Dim i As Long, o As Long
    Dim s As String
    Dim r As Range
    
    Set r = ActiveSheet.Range("C3")     '   <<<<<<<<<<<<<<<<<<<<<<<<< change
    Set r = Range(r, r.End(xlDown))
    
    A = Application.WorksheetFunction.Transpose(r.Value)


    i = A(LBound(A))
    o = 1


    Do While i < A(UBound(A))
        If A(o) <> i Then
            s = s & i & ","
        Else
            o = o + 1
        End If
        
        i = i + 1
    
    Loop
    
    s = Left(s, Len(s) - 1)
    
    MsgBox s
End Sub

[Paul_Hossler]

This code will check the A column, adjust to suit:

Sub missingno()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))
msg = "Numbers missing are: "
For i = 1 To lastrow
   If inarr(i, 1) <> i Then
    msg = msg & i & " , "
   End If
Next i
MsgBox (msg)
End Sub

That assumes that the numbers start at 1, and that are are no multiple skips (10,11,12,15,16, ...)


Capture2.JPG Capture.JPG

[snb]

This code suffices:

Sub M_snb()
    sn = Columns(3).SpecialCells(2, 1)
    sp = Evaluate("transpose(row(" & sn(1, 1) & ":" & sn(UBound(sn), 1) & "))")
    
    For j = 1 To UBound(sn)
       sp(sn(j, 1) - sn(1, 1) + 1) = "_"
    Next
    
    MsgBox Join(Filter(sp, "_", 0), vbLf)
End Sub

[Romulo Avila]

Good afternoon,
There was a situation I didn't expect, having two lines with the same value and when this happens all lines down are considered out of sequence, would there be any way around?
Thanks

[snb]

The give solutions are providing that .
Why don't you use any one of them ?

[Paul_Hossler]

Good afternoon,
There was a situation I didn't expect, having two lines with the same value and when this happens all lines down are considered out of sequence, would there be any way around?
Thanks

Try this

The '18' is in twice and 19,20, and 21 are missing

Capture.JPG

Option Explicit


Sub MissingNumbers()
    Dim A As Variant
    Dim i As Long, o As Long
    Dim s As String
    Dim r As Range
    
    Set r = ActiveSheet.Range("C3")     '   <<<<<<<<<<<<<<<<<<<<<<<<< change
    Set r = Range(r, r.End(xlDown))
    
    A = Application.WorksheetFunction.Transpose(r.Value)


    i = A(LBound(A))
    o = 1


    Do While i < A(UBound(A))
        
        If A(o) <> i Then
            s = s & i & ","
        Else
            Do While A(o) = A(o + 1) And i < A(UBound(A))
                o = o + 1
            Loop
            
            o = o + 1
        End If
        
        i = i + 1
    
    Loop
    
    s = Left(s, Len(s) - 1)
    
    MsgBox s
End Sub

[Romulo Avila]

Good Morning,


All right, it worked out, thank you so much for your support.


Hugs