The other day I received a request from our LAN administrator to write him a script to help weed out some missing numbers is a file. These numbers are staff Ids and he wanted to find out which ones are not in use, so he exported all the used numbers in a file and wanted a script to list all the numbers that are not in use. His exact request is below:
"How are you. Could you please help me to create an macro for excel or similar function for txt file to filter a large set of numbers and bring out the missing number between.
For example, we have 10 numbers listed below, and several numbers are not shown.
1
2
3
5
7
9
11
12
13
14
So , that's 10 numbers shown above, and the missing number are 4,6,8,10 . I need something to filter out and list those missing numbers."
I sat down for about three minutes and came up with this simple script that does the job and I thought I'd share it with you. This subroutine will go thru all numbers in column A on a spreadsheet and list all missing numbers in column C. The numbers has to be sorted first for smalest to largest for this to work.
The Code:
Sub findMissing()
Range("c:c").ClearContents
endRow = ThisWorkbook.Sheets(1).Range("a65536").End(xlUp).Row
curVal = Cells(1, 1)
For i = 2 To endRow
Do While Cells(i, 1) <> curVal + 1
n = n + 1
Cells(n, 3) = curVal + 1
curVal = curVal + 1
Loop
curVal = Cells(i, 1)
Next i
End Sub
After running the Sub you will get a list of the gaps in numbers in column C.
