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.


Befo (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

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


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=552822

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.


Befo (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

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


--
liamsdad
------------------------------------------------------------------------
liamsdad's Profile: http://www.excelforum.com/member.php...o&userid=35388
View this thread: http://www.excelforum.com/showthread...hreadid=552822