## 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

Enter your email address below to download the sample 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.

Disclaimer – Please test the code yourself, it may not work in your environment. All code provided as is without any warranty

Augusto

Hi! I have a sequence of invoice numbers on column “E”, which I want to loop through. Thing is there are 19000+ rows. On column “A”, I have the dates that belong to those invoice numbers. I would like to “tell” the macro: ‘hey, take this date (G2 on sheet2) as init date, and this other (G3 on sheet2) as final date, and then loop through “E” (sheet1) looking for missing invoice numbers ONLY if they respective date on column “A” is >= init date and <= final date.

sheet2

G2 = 01/10/2023 and G3 = 30/10/2023

sheet1

col "A" col "E"

30/09/2023 33998

01/10/2023 33999

01/10/2023 34000

03/10/2023 34001

03/10/2023 34002

03/10/2023 34003

….

The macro works fine, but takes too long looping over rows I'm not interested in. Any help is appreciated in advance :-p

Philip Treacy

Hi Augusto,

Please start atopic on our forum and attach your file. It’ll be easier to help you that way.

Regards

Phil

ben

Hi, This seems to nearly work for what im looking for just a few little changes id like but cant seem to work it out

rather than prompt me for the ranges i want it prefilled with the ranges below

B7:B500,F7:F500,J7:J500,N7:N500,R7:R500,V7:V500,VZ7:Z500

and instead of listing them in a cell can they be listed in a msgbox?

and is there a way to ignore a certain number?

Catalin Bombea

Hi Ben,

Instead of:

Set InputRange = Application.InputBox(Prompt:=”Select a range to check :”, _

Title:=”Find missing values”, _

Default:=Selection.Address, Type:=8)

You can use:

Set InputRange = Activesheet.Range(“B7:B500,F7:F500,J7:J500,N7:N500,R7:R500,V7:V500,Z7:Z500”)

Use an array to store the results:

Dim Arr() as variant, Counter as long

Replace this loop:

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

With this one:

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 to array

redim preserve Arr(0 to Counter)

Arr(Counter)=count_i

Counter=Counter+1

End If

Next count_i

‘Display the results:

MsgBox Join(Arr, “, “)

Morris

Hii I want to code for Multiple data in columns

Like Starting number(1950) in Column A and Ending number(2012) in Column B. Output in Column C

1950 to 2012, 2001-2021, 1958-1999 elc…

Output Like

1950

1951

1952

1953

1954

1955

1956

1957

1958

1959

1960

1961

1962

1963

1964

1965

1966

1967

1968

1969

1970

1971

1972

1973

1974

1975

1976

1977

1978

1979

1980

1981

1982

1983

1984

1985

1986

1987

1988

1989

1990

1991

1992

1993

1994

1995

1996

1997

1998

1999

2000

2001

2002

2003

2004

2005

2006

2007

2008

2009

2010

2011

2012

Catalin Bombea

Hi Morris,

You have to make some changes:

Add this procedure in the same module:

Sub CheckPresetRanges()

Dim i As Long, InputRange As Range, OutputRange As Range

i = 2

Do

Set InputRange = Range(“A” & i & “:B” & i)

Set OutputRange = Range(“C” & i)

FindMissingvalues InputRange, OutputRange

i = i + 1

Loop Until Len(Cells(i, “A”)) = 0

End Sub

The first section of the FindMissingValues procedure needs to be changed, replace the following part:

Sub FindMissingvalues(Optional InputRange As Range = Nothing, Optional OutputRange As Range = Nothing)

Dim 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 = True

On Error GoTo ErrorHandler

‘Ask for the range to check

If InputRange Is Nothing Then 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

If OutputRange Is Nothing Then Set OutputRange = Application.InputBox(Prompt:=”Select where you want the result to go :”, _

Title:=”Select cell for Results”, _

Default:=Selection.Address, Type:=8)

Now you have to assign the procedure CheckPresetRanges to the button.

Jeff H

Thanks for this simple and flexible code. I adapted it for my sudoku spreadsheet to check for eligible candidates in a cell’s three houses (row, column, block). It works perfectly with minor tweaks.

Philip Treacy

Glad you found it useful Jeff.

Manouk

Hi Catalin,

Thanks for the quick reply.

I want to do this with a range from 1-1050.

When I insert this in the formula it goes to -4000 etc.

How should I adjust it with this bigger range?

Thanks!

Catalin Bombea

Hi Manouk,

Not sure what you mean.

the code lists the missing numbers from the range you select, there is no limit set in the code. Make sure you have numbers between 1-1050 in the range you select and it will work.

Manouk

Hi Phil,

I am overwriting my source data ( by using the adjusted formula given below) with the new sequence numbers, however I would like the numbers that are added to have blank columns:

1. Water

2. Elektricity

4. CO2

Now turns into

1. Water

2. Elektricity

3. CO2

However I want it to be

1. Water

2. Elektricity

3.

4.CO2

How would I need to do that?

Thanks!!

Catalin Bombea

Hi Manouk,

You can change this part:

If Horizontal Then

If count_j = 3 Then count_j = 4OutputRange.Cells(NumRows, count_j).Value = count_i

count_j = count_j + 1

I added If count_j = 3 Then count_j = 4, this will create a gap between columns 2 and 4. Change as needed.

Clint

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

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

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

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

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

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:

I’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

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

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

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

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

wau… is excellent . Thank you.

Philip Treacy

Thanks Michael.

Dennis

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

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

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

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

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:

I 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

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

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

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

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.

Philip Treacy

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