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
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.