Solved: Excel insert rows based on value of previous row

[karlos]

Need to get a spread sheet up and running urgently as always however the format we use has been ignored so i now need to insert thousands of lines.

I need to insert rows based on the number of sequential mumbers in previous rows. Cell A8 starts with 1 and carries on down column A with more 1s followed by 2s followed by 3s etc. each group of numbers can be upto 25 no more. Problem is some groups are less than 25 so I need to insert rows to bring the total number of rows for each number to 25.

I have attached a spread sheet to illustrate.

can anyone assits please.

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim NumSame As Long
Dim LastEntry As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastEntry = LastRow + 1
NumSame = 0
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value <> .Cells(i - 1, "A").Value And _
.Cells(i, "A").Value <> "" Then

If NumSame < 25 Then .Rows(LastEntry).Resize(25 - NumSame).Insert
NumSame = 1
LastEntry = i
Else

NumSame = NumSame + 1
End If
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

[karlos]

Many thanks "Oh Guru", does exactly what I asked for. Again many thanks.