Insert rows & values.

Posted by Justin Forbes on July 30, 2001 12:15 PM

I'm just getting started with a small macro, but having a little difficulties with some parts. I have put together a basic example that should get me in the dirrection I need to go.

How can you take a sheet like the following:

1
2
3
5
6
8
9
10

Create a macro that will sequentially move through the rows and insert a row where a value is missing as well as the value. You will notice that the numbers 4 & 7 are not in the column. These should end up being place in the proper place when done.

Thanks..

Justin Forbes

Posted by Cory on July 30, 2001 12:30 PM

Justin,

If the sample data you gave starts in A1, use the following code in your macro:

Range("A2").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
ActiveCell.Offset(1, 0).Select
End If
Loop

Hope this helped,

Cory

Posted by Justin Forbes on July 31, 2001 10:12 AM

This is just great. Thanks a lot..

Justin.