Autonumber With Missing Values

[jorymf8]

I would like to know if there is a way to fill in missing values when using autonumber in Microsoft Excel. For example, I have a list of numbers 1,2,3 etc all the way to 500. In between there are missing values such that it goes 5,6,9,12. Is there a way for excel to recognize these missing values (7,8,10,11) and insert rows for these numbers?

 

[Joe4]

Welcome to the Board!

Are you agreeable to a VBA solution? If you sort them in ascending order, you could loop through them using VBA, and if there are any missing, you could have VBA insert the row.

 

[jorymf8]

Thank you for the response. I am not familiar with VBA, would it be difficult to learn?

 

[Joe4]

It depends on how familiar you are with programming languages, in general. If you have some experience with other programming languages, a lot of concepts may be similar (just different syntax). However, learning that first language usually has a learning curve and will take some time.

However, I am not really asking you to write it. I am just asking you if a VBA solution is acceptable. Some companies have policies against using VBA, so I don't want to waste time devising a solution you cannot use.

If you can use VBA, I can probably come up with the code you need if your provide more details on the layout of your file (what column are these values in?, what are your starting and ending points?, etc).

 

[jorymf8]

Okay great, there is no policy against it. The start date in the first column is 8/15/08, and the end date is 3/14/10. I would like to fill in the missing dates and insert rows for them. In addition, there is a second column I would like to preserve with numbers. So on 8/15/08 the corresponding value is 6. I would like to add the missing rows in between. Thanks again.

 

[Joe4]

Give this VBA code a shot. I am assuming your data start in cell A2.

Sub MyInsertDates()
 
    Application.ScreenUpdating = False
    
    Dim i As Long
        
'   Find last row with data in column A
    i = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows going backwards, inserting rows where needed (assuming data starts on row 2)
    Do Until i = 2
'   If the row above the current row is more than 1 day prior then...
        If Cells(i, "A") - Cells(i - 1, "A") > 1 Then
'   ... insert new row and add date of one day before
            Rows(i).EntireRow.Insert
            Cells(i, "A") = Cells(i + 1, "A") - 1
'   ... otherwise move to the next row up the list
        Else
            i = i - 1
        End If
    Loop
        
    Application.ScreenUpdating = True
    
End Sub

 

[jorymf8]

Thanks a lot!

 

[Joe4]

Glad to help!

 

[jorymf8]

When I input the macro posted above, I get a "runtime error 13", "type mismatch". Do you know what changes I should be making? The data does begin in Row A, cell 2. Thanks again.

 

[Joe4]

Are your date actually stored as dates, or as text (you want date format)?
One easy way to check is this:

If A2 contains your first date, place this formula somewhere in a blank cell:
=ISNUMBER(A2)
If it returns FALSE, the date is entered as text.