The only problem is the macro doesn't add the rows for the last service
group. That is because it can't compare row 12 (the end of data) with
another service group because it is the last one. All I have to do is go
from the active cell up 8 more rows and add 24 rows for the default, it would
stop on the last row of SG01 and compare it with the next row which would be
text and then stop presumably. In this sheet there are 16 rows in SG01. I
want divided in half and 32 rows inserted in the middle.

I should probably have added a loop to count each service group and insert
the rows instead of stepping through the range but its water under the bridge
now.

There has to be a way to put in the necessary rows in the last service
group? " H" column is the service group. It is SG01. It is always SG01 but
on some sheets it is SG001.

tia,

Public Sub n2m4()


Const ServiceGroupColumn As String = "$H"
Const FirstDataRow As Integer = 12


Dim iRow As Long
Dim rowsToAdd As Integer
Dim LastRow As Long
Dim i As Integer
Dim rng As Range
Dim SvcGrpNum As Long
Dim SvcGrp As String

SvcGrpNum = InputBox("Please input the the total number of Service Group
connections from the DNP", "Service Group Number", 48)


With ActiveWorkbook.Worksheets("VOD")
LastRow = .Cells(.Rows.count, ServiceGroupColumn).End(xlUp).Row
i = 1

For iRow = LastRow To (FirstDataRow) Step -1

i = i + 1
If .Cells(iRow, ServiceGroupColumn).Value = .Cells(iRow - 1,
ServiceGroupColumn).Value Then

Else
rowsToAdd = SvcGrpNum - i

Set rng = .Cells(iRow, ServiceGroupColumn)
SvcGrp = rng.Offset(SvcGrpNum / 2, 0).Value
rng.Offset(SvcGrpNum / 2, 0).Resize(rowsToAdd + 1).EntireRow.Insert
rng.Offset(SvcGrpNum / 2, 0).Resize(rowsToAdd + 1).Value = SvcGrp
i = 1

End If

Next iRow

End With
End Sub

Why don't you start the For loop at LastRow + 1

from
For iRow = LastRow To (FirstDataRow) Step -1
to
For iRow = (LastRow + 1) To (FirstDataRow) Step -1

"Janis" wrote:

The only problem is the macro doesn't add the rows for the last service
group. That is because it can't compare row 12 (the end of data) with
another service group because it is the last one. All I have to do is go
from the active cell up 8 more rows and add 24 rows for the default, it would
stop on the last row of SG01 and compare it with the next row which would be
text and then stop presumably. In this sheet there are 16 rows in SG01. I
want divided in half and 32 rows inserted in the middle.

I should probably have added a loop to count each service group and insert
the rows instead of stepping through the range but its water under the bridge
now.

There has to be a way to put in the necessary rows in the last service
group? " H" column is the service group. It is SG01. It is always SG01 but
on some sheets it is SG001.

tia,

Public Sub n2m4()


Const ServiceGroupColumn As String = "$H"
Const FirstDataRow As Integer = 12


Dim iRow As Long
Dim rowsToAdd As Integer
Dim LastRow As Long
Dim i As Integer
Dim rng As Range
Dim SvcGrpNum As Long
Dim SvcGrp As String

SvcGrpNum = InputBox("Please input the the total number of Service Group
connections from the DNP", "Service Group Number", 48)


With ActiveWorkbook.Worksheets("VOD")
LastRow = .Cells(.Rows.count, ServiceGroupColumn).End(xlUp).Row
i = 1

For iRow = LastRow To (FirstDataRow) Step -1

i = i + 1
If .Cells(iRow, ServiceGroupColumn).Value = .Cells(iRow - 1,
ServiceGroupColumn).Value Then

Else
rowsToAdd = SvcGrpNum - i

Set rng = .Cells(iRow, ServiceGroupColumn)
SvcGrp = rng.Offset(SvcGrpNum / 2, 0).Value
rng.Offset(SvcGrpNum / 2, 0).Resize(rowsToAdd + 1).EntireRow.Insert
rng.Offset(SvcGrpNum / 2, 0).Resize(rowsToAdd + 1).Value = SvcGrp
i = 1

End If

Next iRow

End With
End Sub