The Problem
You have a sequence of numbers, and you want to find the numbers that are missing from that sequence. Let's use some VBA.
Watch This Video - It Explains All
This video explains how the code works, how to use it, and how to get the code and put it into your own workbooks.
For best viewing quality: Press play then
- Click the cog and select 720p HD
- Click the icon on the bottom right of the video to view in full screen.
Let's say that your sequence looks like this :
-2 1 4 7 9
We'll take -2 as the start of the sequence and 9 as the end. So the missing numbers are :
-1 0 2 3 5 6 8
This code uses VBA's .Find method on an input range to look for the missing numbers in a sequence. Numbers in the sequence must be integers, i.e. whole numbers like -59, 1, 34, 67932 etc.
This code works in Excel 2007, 2010 and 2013.
Get the workbook here Make sure it saves as a .xlsm
I’ve often seen complicated loops used to work through this type of problem, but when you can use .Find to actually search for the numbers, it makes your life a whole lot easier.
What it can do
You can select a range of any size and the code will look for missing numbers (integers) in that range.
The lowest value in the range is the starting number in the sequence and the highest value is the last number in the sequence.
It works with negative and positive integers in the same sequence.
Numbers in the input range do not have to be sorted in any way.
Numbers in the input range can occur more than once.
The Range to be searched can be on one sheet, and the output can be on another sheet.
It's not limited by the number of rows or columns in the sheet. A solution using Excel formulas to find missing numbers is limited by the number of rows on the worksheet, because the number of rows on the sheet can be used as highest value in the number sequence.
So if you are using formulas, and Excel 2010, you can't look for numbers greater than 1048576 (the maximum number of rows in Excel 2010/2007). If you are using Excel 2003 you can't look for numbers greater than 65536 which is the maximum number of rows in Excel 2003.
This VBA solution uses Double data type variables so can check negative integers between –1.79769313486231E308 and –4.94065645841247E–324, and positive integers between 4.94065645841247E–324 and 1.79769313486231E308
Input Range
You’ll be prompted by Excel for the input range, which is the range to search for the missing numbers. Use your mouse to select the range to be checked.
Output
Missing numbers are listed one number per cell. The default is to output the results into a column.
You can output the results to a row by using your mouse to select a range with more columns than rows.
You don’t need to know the number of columns you will need, just select more columns than rows to indicate you want the result across a single row.
Output always starts in the cell which is at the first row and first column in the range.
How To Get the Code and Use It
You can do one of two things :
1. Copy the Code Below Into Your Own Workbook
Option Explicit
'**********************************************************
' Find missing integer values in a range
' Author : Philip Treacy, MyOnlineTrainingHub
' For more info go to https://www.MyOnlineTrainingHub.com/finding-missing-numbers-in-a-range-using-vba
'**********************************************************
Sub FindMissingvalues()
Dim InputRange As Range, OutputRange As Range, ValueFound As Range
Dim LowerVal As Double, UpperVal As Double, count_i As Double, count_j As Double
Dim NumRows As Long, NumColumns As Long
Dim Horizontal As Boolean
'Default is to output the results into a column
Horizontal = False
On Error GoTo ErrorHandler
'Ask for the range to check
Set InputRange = Application.InputBox(Prompt:="Select a range to check :", _
Title:="Find missing values", _
Default:=Selection.Address, Type:=8)
'Find the lowest and highest values in the range/sequence
LowerVal = WorksheetFunction.Min(InputRange)
UpperVal = WorksheetFunction.Max(InputRange)
'Ask where the output is to go
Set OutputRange = Application.InputBox(Prompt:="Select where you want the result to go :", _
Title:="Select cell for Results", _
Default:=Selection.Address, Type:=8)
'Check the number of rows and columns in the output range
NumRows = OutputRange.Rows.Count
NumColumns = OutputRange.Columns.Count
'If there are more columns selected than rows, output is to go horizontally
If NumRows < NumColumns Then
Horizontal = True
'Reset the number of rows to 1 so that output is into first row
NumRows = 1
Else
'Reset the number of columns to 1 so that output is into first column
NumColumns = 1
End If
'Initalise counter and loop through sequence from lowest to highest value
count_j = 1
For count_i = LowerVal To UpperVal
'Search for the current value (count_i)
Set ValueFound = InputRange.Find(count_i, LookIn:=xlValues, LookAt:=xlWhole)
'If it's not found, we have a missing number in the sequence
If ValueFound Is Nothing Then
'Output the missing number to the sheet
If Horizontal Then
OutputRange.Cells(NumRows, count_j).Value = count_i
count_j = count_j + 1
Else
OutputRange.Cells(count_j, NumColumns).Value = count_i
count_j = count_j + 1
End If
End If
Next count_i
Exit Sub
ErrorHandler:
If InputRange Is Nothing Then
MsgBox "ERROR : No input range specified."
Exit Sub
End If
If OutputRange Is Nothing Then
MsgBox "ERROR : No output cell specified."
Exit Sub
End If
MsgBox "An error has occurred. The macro will end."
End Sub
In Excel press Alt+F11 to open your Visual Basic Editor.
In the VBA Project window, right click your workbook and click on Insert -> Module
Copy and paste my code into your new module.
Save your workbook as a .xlsm file
2. Save This Workbook
Click here for the workbook Make sure it saves as a .xlsm
Once saved, open it up, enable macros, then press ALT+F11 to open the Visual Basic editor.
In the Project window double click the Modules and then Module1 to see the code.
How to Run The Code
You have a number of options here. What I have done is insert a button and then assign the macro to the button. So when you click the button the macro is executed.
You can do this too, or you could create a shortcut key sequence to run the macro.
To create the shortcut key sequence, go to the Developer tab -> Macros, select the macro FindMissingValues then click on the Options button.
Press the keys you want for the shortcut e.g. CTRL+SHIFT+G and click on OK. You can now close the Macro window and your shortcut will execute when you next press your shortcut key sequence.
Alternative Formula Solution
If you don’t fancy using VBA, have a look at an alternative solution that uses Excel formulas to find missing numbers in a sequence.
Modifications and Enhancements
If you have your own VBA code for doing this I’d love to see it.
Or if you have used my code and modified it to suit your own needs, please post the code in the comments below so others can see what you have done, and they may find it useful too.
Spread the Word
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+1, Facebook and Twitter.
Disclaimer – Please test the code yourself, it may not work in your environment. All code provided as is without any warranty
Clint says
Hi Philip Treacy
This code works very well. Is there a way to make it a 3 step process.
Step 1 – Enter the Minimum and Maximum number
Step 2 – Select the range to search
Step 3 – Select where the result must go
Catalin Bombea says
Hi Clint,
The Min and Max are calculated based on the values from the range selected. If you want to manually type them, the code may not work as you expect.
Replace:
LowerVal = WorksheetFunction.Min(InputRange)
UpperVal = WorksheetFunction.Max(InputRange)
With 2 input boxes, or refer to cels in a sheet:
LowerVal = ThisWorkbook.Worksheets(“Sheet1”).Range(“A1”)
UpperVal = ThisWorkbook.Worksheets(“Sheet1”).Range(“B1”)
For the range to search, you already have an input box, same for selecting the result.
Clint Correia says
Hi Catalin
Thanks for the prompt response. My apologies for not conveying the scenario as I need it. here with the solution I need.
Step 1 – Input box saying “Type in Min Value:”
Step 2 – Input box saying “Type in Max Value:”
Step 3 – Input box saying “Select range to check:”
Step 4 – Input box saying “Select where you want the results to go:”
Catalin Bombea says
Hi Clint,
Just replace:
LowerVal = WorksheetFunction.Min(InputRange)
UpperVal = WorksheetFunction.Max(InputRange)
With:
LowerVal = Application.InputBox(Prompt:=”Type the Min Value:”, Type:=1)
UpperVal = Application.InputBox(Prompt:=”Type the Max Value:”, Type:=1)
For Input and Output Ranges, there are already InputBoxes set.
Mort Wakeland, Dallas, TX says
OH, hindsight is the best sight Philip, Mynda, & Catalin. Just thought of this as I’m looking at my “real, honest-to-God, life example. Not only find the missing number or numbers but then to insert that number or those numbers in the correct position(s) in the sequence. For example:
2010, 2011, _____, 2013, 2014, 2015
would result in
2010, 2011, 2012, 2013, 2014, 2015
but flexi enough to be either by row or by column.
Thanks for any suggestions and may help others out as well?
Philip Treacy says
Hi Mort,
If you want to print all of the numbers in the sequence including the missing ones, all you need to do is count from the lowest number to the highest and print every one of them.
I’m not sure that printing these over the top of your original data is a good idea though, I’d print them elsewhere and then you can use them as you wish.
Overwriting your source data would also require a lot more changes to the code. As it is, just printing out each number only requires a few lines of code to be commented out. Find this section of code and replace with what you see here:
For count_i = LowerVal To UpperVal 'Search for the current value (count_i) 'Set ValueFound = InputRange.Find(count_i, LookIn:=xlValues, LookAt:=xlWhole) 'If it's not found, we have a missing number in the sequence 'If ValueFound Is Nothing Then 'Output the missing number to the sheet If Horizontal Then OutputRange.Cells(NumRows, count_j).Value = count_i count_j = count_j + 1 Else OutputRange.Cells(count_j, NumColumns).Value = count_i count_j = count_j + 1 End If 'End If Next count_iI’ve commented out the code that checks if a number is missing, so the code just prints all numbers.
Regards
Phil
Mort Wakeland, Dallas, TX says
This appears to work but only if the missing number is within the sequence of numbers. In other words, the missing number cannot be at the beginning of the sequence nor at the end. For example, I have a 5 year sequence for which I want to do something with. The beginning year is 2010. So the full sequence would be 2010, 2011, 2012, 2013, and 2014. However, 2014 is missing data and is not display from the data pull. Only 2010, 2011, 2012, and 2013 show up. If I name the range RY (reporting year) and I know I want a 5 year sequence, how to alter the code to include end numbers, in our case here, either a missing 2010, or a missing 2014. See Mynda? From Mort in Dallas
Philip Treacy says
Hi Mort,
There has to be upper and lower limits for the sequence of numbers and logically this is the largest and smallest numbers.
In this case as we are only talking about 5 numbers, to do what you want is a simple change to the code. But for longer sequences I probably wouldn’t do it this way.
All you need to do is modify two lines of code. Find these in the VBA and replace them with these two lines:
All I have done is subtract 1 from the lowest value and add 1 to the highest value.
Regards
Phil
Kevin says
Hi i have a sequence of data (months) for a given period and i want to find the missing month in between. The above code is for numbers only. How can i find the missing months in between the sequence. Thanks
Catalin Bombea says
Hi Kevin,
Dates are numbers too, you can extract the month number with =MONTH(A1), then copy down the formula to your range, and run the code for the column where you have the months.
The code should work fine.
Cheers,
Catalin
Michael Bricnes says
wau… is excellent . Thank you.
Philip Treacy says
Thanks Michael.
Dennis says
RE: VBA cide fior missing numbers in a sequence.
VBA code works very well. I am able to produce list of missing numbers, however, the run time seems rather long. I have a column of of 93,000 sorted, 6 digit numbers. When the VBA code is executed, the designated column begins to populate quickly, but the code keeps exercising for a long time. (more than 30 minutes. I broke the original column into seperate 20M row columns and found that there were 480 missing numbers out of the 93M original list. Is there a limitation on the VBA code?
Thank you for your comments.
Philip Treacy says
Hi Dennis,
Could you please open a Helpdesk ticket and send me the workbook so I can have a look at it?
Thanks
Phil
RAMESH says
I wonder whether u ever thought of building a accounting package for small enterprises using excel, right upto the formation of balance sheet and profit and loss statement.
Mynda Treacy says
Hi Ramesh,
I get asked this question from time to time, but since there are many free/affordable accounting packages available online these days it wouldn’t be worth the time to build one from scratch in Excel.
Kind regards,
Mynda.
Bryan says
Tiny typo: You said “If you are using Excel 2007 you can’t look for numbers greater than 65536 which is the maximum number of rows in Excel 2007.” I think you meant 2003 here. The maximum was increased to 1,048,576 with ’07, not ’10.
The .Find approach is interesting. My first instinct would probably be to use a collection item. Add everything to the collection (ignoring duplicate key errors) then loop through the collection and print any error to the screen. In pseudo-code it would look like:
Dim Col as New Collection ' Add to collection For each Rng in InputRange ' Ideally one would use a variant array instead of a range On Error Resume Next Col.Add Rng, CStr(Rng) On Error GoTo 0 Next Rng ' Loop through collection On Error Resume Next For i = Min to Max dummyvar = Col(CStr(i) If Err0 then {Print i to next output row} Err.Clear End if Next i On Error GoTo 0 Next iI obviously haven’t tested it, but I use the collection object a lot when working with unique lists of things. Considering it’s a little brute-force-y by using all the error handling to do your work, I’ve consistently found it to be one of the faster methods. I’d be interested to see how it stacks up speed-wise against the .Find method.
Philip Treacy says
Hi Bryan,
Thank for spotting the 2007/2003 typo.
I’m sure your solution would work too, I just wanted to avoid using lots of loops and if the in-built .Find does the job of searching for me, then I’m more than happy to use it.
Yes it would be interesting to see which approach is faster for larger amounts of data. Let me know if you turn your pseudo code into working VBA and do some tests.
Cheers
Phil
Bryan says
Just for kicks I ran a little comparison program. The collection method is significantly faster, and it breaks away from the .Find method at an increasing rate the more records you have to go through. If you only have a list from 1 to 1 (a trival example, to be sure), the collection method was 4.6 times faster over 10 trials (though the times we are talking here are imperceptible); if your numbered list goes from 1 to 10,000, the collection method is over 300x faster (0.2 seconds vs 55 seconds)! I had no idea the differential would be that great. (With the method I used, about 37% of the numbers were missing; so for e.g. the 100 record case, around 37 numbers between 1 and 100 had to be filled in).
I was using randomized data in an overly simplified example, so it might not be broadly applicable, but I can’t think of a situation that would favor the .Find method over collection, at least as written. I’m sure it could be optimized to bring the time down as well.
I can post the code if you are interested. (Btw, thanks for fixing the formatting on my last comment!)
Philip Treacy says
Hi Bryan,
Thanks for taking the time to check this out. I am surprised, and disappointed, that an in-built method is slower than something one has to write oneself. Maybe Microsoft can hire you to improve their code? 🙂
Yes please, post your code, would be good to see what you have done. (no worries with the formatting – you can use <pre> </pre> tags to wrap the code)
I guess from my point of view, and the reason I did it this way, using .Find was easier than writing loops, and hopefully for others not as well versed in VBA, easier to understand and implement. And as long as there aren’t too many numbers involved, the increased time wouldn’t really be a problem.
Cheers
Phil
Bryan says
Hey if MS is reading this and wants to hire me feel free to contact me 🙂
Looking at the numbers again, the results don’t become perceptible until ~1,000 records (~0.5 seconds), annoying until ~4500 records (~10 seconds), or unbearable until ~11,000 records (~1 min), so for any reasonable search either method is fine. I tend to work with huge number of records (or huge numbers of loops), so I find I’m constantly trying to find new methods to shave off that last imperceptible amount of time because it has a tendancy to blow up on me.
.Find is definitely easy and sometimes finding a substitute for it is really difficult or cumbersome. In an office environment, often times easy-to-read is a good compromise when otherwise users would have to go to you to figure out how the code works!
At any rate, here’s my code. I use the RANDBETWEEN() worksheet function go generate the data, and instead of outputting to a cell (which just takes time but takes the same amount of time no matter which method is used) I increase a dummy variable. The random cells only refresh between trials, so the two methods have the same number of missing/repeated numbers.
Option Explicit Private Declare Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long Private Declare Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long Function MicroTimer() As Double ' Taken from "Improving Performance in Excel 2007" ' http://msdn.microsoft.com/en-us/library/aa730921.aspx ' Returns seconds. Dim cyTicks As Currency Static cyFrequency As Currency MicroTimer = 0 ' Get frequency. If cyFrequency = 0 Then Call getFrequency(cyFrequency) ' Get ticks. Call getTickCount(cyTicks) If cyFrequency Then MicroTimer = cyTicks / cyFrequency End Function Sub CompareMethods() Dim lngNumValues() As Long, lngNumRuns As Long Dim strBorder As String Dim lngMin As Long, lngMax As Long Dim rng As Range, rngInput As Range, rngFind As Range Dim col As Collection Dim i As Long, j As Long, k As Long Dim dblFindTime As Double, dblColTime As Double Dim dblFindAvg As Double, dblColAvg As Double Dim lngDummy1 As Long, lngDummy2 As Long, varDummy As Variant '--------------------------------- ' CHANGE THIS PART TO DEFINE RUNS '--------------------------------- lngNumRuns = 10 ReDim lngNumValues(1 To 5) As Long lngNumValues(1) = 1 lngNumValues(2) = 10 lngNumValues(3) = 100 lngNumValues(4) = 1000 lngNumValues(5) = 11 '--------------------------------- ' THIS IS WHERE THE MAGIC HAPPENS '--------------------------------- ' Set up output table headers strBorder = String(92, "-") Debug.Print "Values", "Find", "Rec/Sec", "Col", "Rec/Sec", "Find-Col", "Find/Col" Debug.Print strBorder For i = LBound(lngNumValues) To UBound(lngNumValues) For k = 1 To lngNumRuns 'Set up random numbers lngMin = 1 lngMax = lngNumValues(i) Cells.Clear Set rngInput = Range("A1").Resize(lngMax, 1) rngInput.Formula = "=RANDBETWEEN(" & lngMin & "," & lngMax & ")" ' Method 1 (.Find) dblFindTime = MicroTimer For j = lngMin To lngMax Set rngFind = rngInput.Find(j, LookIn:=xlValues, lookat:=xlWhole) If rngFind Is Nothing Then lngDummy1 = lngDummy1 + 1 End If Next j dblFindTime = MicroTimer - dblFindTime ' Method 2 (Collection Object) dblColTime = MicroTimer Set col = New Collection ' First add all items to collection (ignore errors when duplicates are added) For Each rng In rngInput On Error Resume Next col.Add rng.Value2, CStr(rng.Value2) On Error GoTo 0 Next rng ' Loop through every possible nubmer in range (error returned when number not found) On Error Resume Next For j = lngMin To lngMax varDummy = col(CStr(j)) If Not Err.Number = 0 Then lngDummy2 = lngDummy2 + 1 Err.Clear End If Next j On Error Resume Next ' Print timings and add current run to average dblColTime = MicroTimer - dblColTime dblFindAvg = dblFindAvg * (k - 1) / k + dblFindTime / k dblColAvg = dblColAvg * (k - 1) / k + dblColTime / k Debug.Print Format(lngMax, "#,##0"), _ Format(dblFindTime, "#,##0.00000"), _ Format(lngMax / dblFindTime, "#,##0"), _ Format(dblColTime, "#,##0.00000"), _ Format(lngMax / dblColTime, "#,##0"), _ Format(dblFindTime - dblColTime, "+#,##0.00000;-#,##0.00000"), _ Format(dblFindTime / dblColTime, "#,##0.00") Next k ' Pring average for the number of rows Debug.Print strBorder Debug.Print Format(lngMax, "#,##0 AVG"), _ Format(dblFindAvg, "#,##0.00000"), _ Format(lngMax / dblFindAvg, "#,##0"), _ Format(dblColAvg, "#,##0.00000"), _ Format(lngMax / dblColAvg, "#,##0"), _ Format(dblFindAvg - dblColAvg, "+#,##0.00000;-#,##0.00000"), _ Format(dblFindAvg / dblColAvg, "#,##0.00") Debug.Print strBorder Debug.Print Next i Debug.Print "Check: ", lngDummy1 & " = " & lngDummy2, lngDummy1 = lngDummy2 End SubPhilip Treacy says
Hi Bryan,
If you are working with large amounts of data as you are, I can understand the desire to shave off as much time as possible 🙂
Thanks for the code, I’ll give it a spin.
Regards
Phil