• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Finding Missing Numbers in a Range Using VBA

You are here: Home / Excel VBA / Finding Missing Numbers in a Range Using VBA
Finding Missing Numbers in a Range Using VBA
September 11, 2013 by Philip Treacy

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

  1. Click the cog and select 720p HD
  2. 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.

  • The range can be multiple rows and/or columns.
  • It must be contiguous.
  • It can be the entire worksheet if you like.

  • 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

    How to insert a VBA module

    Copy and paste my code into your new module.

    Save your workbook as a .xlsm file

    Save Excel workbook as .xlsm

    2. Save This Workbook

    Enter your email address below to download the sample workbook.

    By submitting your email address you agree that we can email you our Excel newsletter.
    Please enter a valid email address.

    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

    Finding Missing Numbers in a Range Using VBA

    More Excel VBA Posts

    automating and emailing pivot table reports

    Automating and Emailing Pivot Table Reports

    Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
    Checking values in range objects with vba

    Checking Values in Range Objects With VBA

    Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
    Static variables in VBA

    Static Variables in VBA

    Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
    save chart as image

    Save Chart as Image

    List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
    Clearing Downstream Dependent Data Validation Lists

    Clear Downstream Dependent Data Validation Lists

    Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
    Excel Status Bar

    Excel Status Bar

    Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
    Progress Bar for Excel VBA

    Excel Progress Bar for VBA

    Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
    error handling in vba

    Error Handling in VBA

    Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
    Finding File Metadata Using FileSystemObject

    Finding File Meta Data Using FileSystemObject

    Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
    Automatically Add Items to Data Validation List

    Automatically Add Items to Data Validation List

    Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points

    More Excel VBA Posts

    Display All Matches from Search in Userform ListBox

    Display All Matches from Search in Userform ListBox

    Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
    animating excel charts

    Animating Excel Charts

    Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
    dynamic data validation lists in userforms

    Dynamic Data Validation Lists in Userforms

    Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
    show report filter pages for power pivot pivottables

    Show Report Filter Pages for Power Pivot PivotTables

    PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
    charting real time data in excel

    Charting Real Time Data in Excel

    Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
    select multiple items from drop down data validation list

    Select Multiple Items from Drop Down (Data Validation) List

    Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
    Excel Calendar (Date Picker) to Use in Worksheets and Userforms

    Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

    Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
    automating and emailing pivot table reports

    Automating and Emailing Pivot Table Reports

    Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
    search for data with userform

    Searching for Data With a User Form

    Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
    Checking values in range objects with vba

    Checking Values in Range Objects With VBA

    Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


    Category: Excel VBATag: Excel VBA
    Previous Post:Excel Formula - List Missing Numbers in a SequenceExcel Formula – List Missing Numbers in a Sequence
    Next Post:Filter Excel Pivot Tables by ValuesFilter Excel Pivot Tables by Values

    Reader Interactions

    Comments

    1. ben

      August 29, 2022 at 9:53 pm

      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?

      Reply
      • Catalin Bombea

        August 29, 2022 at 11:12 pm

        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, “, “)

        Reply
    2. Morris

      November 17, 2021 at 9:17 pm

      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

      Reply
      • Catalin Bombea

        November 21, 2021 at 12:57 am

        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.

        Reply
    3. Jeff H

      March 31, 2021 at 12:08 am

      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.

      Reply
      • Philip Treacy

        April 1, 2021 at 3:31 pm

        Glad you found it useful Jeff.

        Reply
    4. Manouk

      March 19, 2019 at 9:34 pm

      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!

      Reply
      • Catalin Bombea

        March 19, 2019 at 10:27 pm

        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.

        Reply
    5. Manouk

      March 18, 2019 at 11:59 pm

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

      Reply
      • Catalin Bombea

        March 19, 2019 at 2:21 pm

        Hi Manouk,
        You can change this part:

        If Horizontal Then
        If count_j = 3 Then count_j = 4
        OutputRange.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.

        Reply
    6. Clint

      June 19, 2017 at 7:39 pm

      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

      Reply
      • Catalin Bombea

        June 20, 2017 at 2:13 pm

        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.

        Reply
        • Clint Correia

          June 22, 2017 at 12:23 am

          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:”

          Reply
          • Catalin Bombea

            June 22, 2017 at 6:42 pm

            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.

            Reply
    7. Mort Wakeland, Dallas, TX

      September 13, 2016 at 4:01 am

      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?

      Reply
      • Philip Treacy

        September 13, 2016 at 9:50 am

        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_i
        

        I’ve commented out the code that checks if a number is missing, so the code just prints all numbers.

        Regards

        Phil

        Reply
    8. Mort Wakeland, Dallas, TX

      September 13, 2016 at 3:50 am

      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

      Reply
      • Philip Treacy

        September 13, 2016 at 9:43 am

        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:

        LowerVal = WorksheetFunction.Min(InputRange) - 1
        UpperVal = WorksheetFunction.Max(InputRange) + 1
        

        All I have done is subtract 1 from the lowest value and add 1 to the highest value.

        Regards

        Phil

        Reply
    9. Kevin

      March 31, 2016 at 9:03 pm

      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

      Reply
      • Catalin Bombea

        April 1, 2016 at 1:20 am

        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

        Reply
    10. Michael Bricnes

      June 2, 2015 at 3:49 am

      wau… is excellent . Thank you.

      Reply
      • Philip Treacy

        June 2, 2015 at 8:33 am

        Thanks Michael.

        Reply
    11. Dennis

      February 7, 2015 at 3:21 am

      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.

      Reply
      • Philip Treacy

        February 8, 2015 at 9:16 am

        Hi Dennis,

        Could you please open a Helpdesk ticket and send me the workbook so I can have a look at it?

        Thanks

        Phil

        Reply
    12. RAMESH

      September 22, 2013 at 2:21 pm

      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.

      Reply
      • Mynda Treacy

        September 23, 2013 at 12:54 pm

        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.

        Reply
    13. Bryan

      September 12, 2013 at 5:16 am

      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 i
      

      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.

      Reply
      • Philip Treacy

        September 12, 2013 at 9:13 am

        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

        Reply
        • Bryan

          September 13, 2013 at 5:10 am

          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!)

          Reply
          • Philip Treacy

            September 13, 2013 at 9:51 pm

            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

            Reply
            • Bryan

              September 17, 2013 at 12:29 am

              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"
              '             https://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 Sub
              
            • Philip Treacy

              September 17, 2013 at 9:24 pm

              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

    Leave a Reply Cancel reply

    Your email address will not be published. Required fields are marked *

    Current ye@r *

    Leave this field empty

    Sidebar

    More results...

    Featured Content

    • 10 Common Excel Mistakes to Avoid
    • Top Excel Functions for Data Analysts
    • Secrets to Building Excel Dashboards in Less Than 15 Minutes
    • Pro Excel Formula Writing Tips
    • Hidden Excel Double-Click Shortcuts
    • Top 10 Intermediate Excel Functions
    • 5 Pro Excel Dashboard Design Tips
    • 5 Excel SUM Function Tricks
    • 239 Excel Keyboard Shortcuts

    100 Excel Tips and Tricks eBook

    Download Free Tips & Tricks

    Subscribe to Our Newsletter

    Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

    We respect your email privacy

    Guides and Resources

    • Excel Keyboard Shortcuts
    • Excel Functions
    • Excel Formulas
    • Excel Custom Number Formatting
    • ALT Codes
    • Pivot Tables
    • VLOOKUP
    • VBA
    • Excel Userforms
    • Free Downloads

    239 Excel Keyboard Shortcuts

    Download Free PDF

    Free Webinars

    Excel Dashboards Webinar

    Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

    Click Here to Watch Now

    mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

    Blog Categories

    • Excel
    • Excel Charts
    • Excel Dashboard
    • Excel Formulas
    • Excel PivotTables
    • Excel Shortcuts
    • Excel VBA
    • General Tips
    • Online Training
    • Outlook
    • Power Apps
    • Power Automate
    • Power BI
    • Power Pivot
    • Power Query
    microsoft mvp logo
    trustpilot excellent rating
    Secured by Sucuri Badge
    MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
     

    Company

    • About My Online Training Hub
    • Disclosure Statement
    • Frequently Asked Questions
    • Guarantee
    • Privacy Policy
    • Terms & Conditions
    • Testimonials
    • Become an Affiliate

    Support

    • Contact
    • Forum
    • Helpdesk – For Technical Issues

    Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.