I have a range of numbers from, let's say, 1 to 10000. But there are numbers missing. What would be a solution to find the missing numbers and put them in a column?
Dim Flag as Boolean
For i = 1 to 10000
Flag = False
For j = 1 to ActiveSheet.UsedRange.Rows.Count
If Range("A" & j).Value = i Then
Flag = True
Exit For
End if
Next
If Flag = False
Range("B1").End(xldown).Offset(1,0).Value = i
End If
Next
A | B | |
1 | Missing | |
2 | 1 | 8 |
3 | 2 | 10 |
4 | 3 | 11 |
5 | 4 | 13 |
6 | 5 | 14 |
7 | 5 | |
8 | 6 | |
9 | 7 | |
10 | 9 | |
11 | 12 | |
12 | 15 |
A small sample would be very helpful...
We have say in A2:A5...
I guess Other's solution are much better ...
Sub MissingNumbers()
Dim X As Long, Max As Long, Given As Variant, Missing As Variant
Given = Range("A1", Cells(Rows.Count, "A").End(xlUp))
ReDim Missing(1 To Given(UBound(Given), 1))
For X = 1 To UBound(Missing)
Missing(X) = X
Next
For X = 1 To UBound(Given)
Missing(Given(X, 1)) = ""
Next
Missing = Application.Transpose(Split(Application.Trim(Join(Missing))))
Range("B1").Resize(UBound(Missing)) = Missing
End Sub