Automatically Insert Rows

  • Thread starter Thread starter Markus
  • Start date Start date
M

Markus

I have a file with information set up in a sheet like this
Number Name Code
1001 Eric Blue
1003 Jenny Red
1004 Alex Green
1007 James Blue

I want to automatically insert rows where the number skips
and enter in the next sequential number.
Number Name Code
1001 Eric Blue
1002
1003 Jenny Red
1004 Alex Green
1005
1006
1007 James Blue

Any help would be greatly appreciated.
 
Markus,

assuming your "numbers" column is in the range A1:A...
then this nasty little routine should sort it out for you.
If you have any problems getting it to run, it may be
because the Usedrange is different from what you think. If
that happens, replace "LastRow" in the "For.." statement
with the number of the last line in your list:

Sub AddRows()

LastRow = ActiveSheet.UsedRange.Rows.Count
For x = LastRow To 2 Step -1

If Cells(x, 1).Value <> (Cells(x - 1, 1).Value + 1) Then
Rows(x).Insert
Cells(x, 1).Value = Cells(x + 1, 1).Value - 1
x = x + 1
End If


Next x

End Sub
 
Back
Top