PDA

View Full Version : [SOLVED] Identify missing numbers in sequence



Romulo Avila
10-16-2019, 01:52 PM
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
10-16-2019, 03:00 PM
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
10-16-2019, 03:31 PM
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
10-16-2019, 03:39 PM
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, ...)


25294 25293

snb
10-17-2019, 01:55 AM
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
10-18-2019, 12:58 PM
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
10-20-2019, 03:02 AM
The give solutions are providing that .
Why don't you use any one of them ?

Paul_Hossler
10-20-2019, 08:28 AM
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

25313





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
10-21-2019, 04:37 AM
Good Morning,


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


Hugs