insert rows

  • Thread starter Thread starter dave!!
  • Start date Start date
D

dave!!

i have several columns of information, one of them being "claim number" and
another being "report number" (report numbers are 1 through 5...there is
always at least a 1, or maybe 1 and 2...etc) I need to have 5 instances of
each claim number. What code could i use so that it would look at the
report number and if its a "1" then look at the next line and if that is
also a "1" insert 4 rows below it, if the report number is a "1" and next
line is a "2" then insert 3 rows below it...etc. Basically I want the macro
to scan the column and each time there is a "1" to look at the previous line
to get report value "x", get the report number and insert 5-x rows below it.

if anyone needs clarification let me know.

Thanks,
Dave
 
Sub AddLines()
Dim rng As Range
Dim i As Long
Dim num As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)

For i = rng.Row To 2 Step -1
If Cells(i + 1, 1).Value <> Cells(i, 1).Value And _
Cells(i + 1, 1) <> "" Then
num = 5 - Cells(i, 2).Value
If num > 0 Then
Cells(i + 1, 1).Resize(num).EntireRow.Insert
End If
End If
Next

End Sub


Might be what you want.

Test on a copy of your data.
 
Back
Top