How to auto insert rows in a list of numbers

Z

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
 
M

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
 
L

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.
 
L

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

GETPIVOTDATA 0
Pulling data from sheet to mastersheet 6
Custom HEX or BASE 33 Sequence 7
Excel function, incrementing value 7
replacing data 4
Missing #'s 2
SQL Statement 1
LAST of MAX records in query 1

Top