How to auto insert rows in a list of numbers

I am working with a large list of numbers (between 0000 and 9999). The list has breaks in it that I need to fill without disturbing the data assigned to specific numbers. My question is if there is a way to get excel to automatically insert rows in this list to fill these gaps. This would save me many hours of work. Thank you in advance! Example below.


Before: (what I have)

0001 ABC
0005 DEF
0006 GHI
0007 KLM
00011 NOP

After: (what I need)

0001 ABC
0002
0003
0004
0005 DEF
0006 GHI
0007 KLM
0008
0009
0010
00011 NOP

Joe Z

Sub FillBlanks()
Set topcell = Cells(1, ActiveCell.Column)
Set bottomcell = Cells(16384, ActiveCell.Column)
If IsEmpty(topcell) Then Set topcell = topcell.End(xlDown)
If IsEmpty(bottomcell) Then Set bottomcell = bottomcell.End(xlUp)
Range(topcell, bottomcell).Select
Selection.SpecialCells(xlBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub

There will be better ways to so it than this, but i would import the data.
Then open a second sheet, manually input the numbers using autofill. The
insert a vlookup into column B to return the correct letters against the
correct number. Then copy and paste special the values of each cell into a
third sheet,

A very lengthy way, but this would produce what you are looking for.

"zdek" wrote:

>
> I am working with a large list of numbers (between 0000 and 9999). The
> list has breaks in it that I need to fill without disturbing the data
> assigned to specific numbers. My question is if there is a way to get
> excel to automatically insert rows in this list to fill these gaps.
> This would save me many hours of work. Thank you in advance! Example
> below.
>
>
> Before: (what I have)
>
> 0001 ABC
> 0005 DEF
> 0006 GHI
> 0007 KLM
> 00011 NOP
>
> After: (what I need)
>
> 0001 ABC
> 0002
> 0003
> 0004
> 0005 DEF
> 0006 GHI
> 0007 KLM
> 0008
> 0009
> 0010
> 00011 NOP
>
> Joe Z
>
>
> --
> zdek
> ------------------------------------------------------------------------
> zdek's Profile: http://www.excelforum.com/member.php...o&userid=35510
> View this thread: http://www.excelforum.com/showthread...hreadid=552822
>
>