How to identify missing numbers sequence in Excel?

[boboivan]

Hello everybody!

To identify missing numbers sequence, I currently use this code (made by a smart guy):

Sub Missingvalues()
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 = Application.InputBox(Prompt:="Select a range:", _
Title:="Extract missing values", _
Default:=Selection.Address, Type:=8)
StartV = InputBox("Start value:")
EndV = InputBox("End value:")
On Error GoTo 0
Set WS = Sheets.Add
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") = "Missing values"
WS.Range("B2:B" & UBound(k) + 1) = Application.Transpose(k)
End Sub

As I have no idea about how to create a VBA code, could you please let me know if there is any chance to adapt this code to my needs as follows:

1. the selected range to be by default (e.g. $C$2:$C$5000);
2. the new worksheet to have only the column with the missing numbers (and without "missing values" header);
3. to be able to add exceptions (e.g. numbers 4550432, 4552357, etc) that will not displayed in the new worksheet;
4. if some missing numbers are subsequently added in the original worksheet, these numbers to be automatically erased from the new worksheet, without running again the macro;
5. when I run again the macro, to create the same worksheet, with a particularly name.

Thank you so much in advance!

 

[mirabeau]

Hello everybody!

To identify missing numbers sequence, I currently use this code (made by a smart guy):
...
As I have no idea about how to create a VBA code, could you please let me know if there is any chance to adapt this code to my needs as follows:

1. the selected range to be by default (e.g. $C$2:$C$5000);
2. the new worksheet to have only the column with the missing numbers (and without "missing values" header);
3. to be able to add exceptions (e.g. numbers 4550432, 4552357, etc) that will not displayed in the new worksheet;
4. if some missing numbers are subsequently added in the original worksheet, these numbers to be automatically erased from the new worksheet, without running again the macro;
5. when I run again the macro, to create the same worksheet, with a particularly name.

Thank you so much in advance!

do you have a particular attachment to the code you posted?

An alternative is listed below that supplies missing numbers.
1. Your specified range is included at the top of the code
2. Missing numbers are listed in Col E of the same worksheet, but they can easily be put in new worksheet if wanted
3. Exceptions easily allowed for. But how/where are their values to be specified?
4. Not immediately obvious to me how this might be done. But re-running the macro below is very quick.
5. Not clear to me what you mean by this. Could you please elucidate?

Sub missing()

Const rng As String = "$C$2:$C$5000"
Dim d As Object, a, c()
Dim i As Long, mx As Long, mn As Long

Set d = CreateObject("scripting.dictionary")
a = Range(rng)
mx = Application.Max(a): mn = Application.Min(a)
ReDim c(1 To mx - mn + 1, 1 To 1)

For i = 1 To UBound(a): d(a(i, 1)) = 1: Next i

For i = mn To mx
    If d(i) <> 1 Then k = k + 1: c(k, 1) = i
Next i

Range("E1").Resize(k) = c

End Sub

 

[boboivan]

Then click OK, and enter the start value of your number sequence in the pop outing box.

And go on clicking OK, enter the end value of the number sequence. See screenshot:

Then click OK, and the missing numbers have been listed into a new worksheet. See screenshot:

 

[boboivan]

2. The missing numbers could be in the same sheet. In this case, point 5 doesn't need to be solved.
3. The exceptions values should not be listed in the column with the missing numbers. These exceptions could be specified into the code.
4. You are right, re-runnig the macro is quick, so don't bother with this matter anymore!