How to auto insert rows in a list of numbers

[zdek]

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

 

[mrice]

Try this macro

Sub Test()
For N = Cells(65536, 1).End(xlUp).Row To 2 Step -1
If Cells(N, 1) <> Cells(N - 1, 1) + 1 Then
Rows(N).Insert
Cells(N, 1) = Cells(N + 1, 1) - 1
N = N + 1
End If
Next N
End Sub

 

[luvthavodka]

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.

 

[liamsdad]

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