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.
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
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.
The range can be multiple rows and/or columns.
It must be contiguous.
It can be the entire worksheet if you like.
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 http://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
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
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