adding some rows in the middle of adding some other rows

[Janis]

This macro does half of what I want it to do. I need it also to add the same
number of rows as on the insert line only this time in the middle of the
service group. Since it already added half of the rows at the end of the
first service group
how do I get it to go to the middle of the service group add the same number
of rows and not get out of step for the next service group?

tia,


Public Sub n2m4()

'adds new rows for connections to VOD sheet
' default total rows to add 40

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 Long

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


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

' If ((SvcGrpNum - i) / 2) And 1 Then
' SvcGrp = .Cells(iRow, ServiceGroupColumn).Value
' MsgBox ("Service Group" & " " & SvcGrp & " " & "has an odd
number of rows. Please add a row and start over.")
' Else

rowsToAdd = (SvcGrpNum - i) / 2


.Cells(iRow, ServiceGroupColumn).Resize(rowsToAdd).EntireRow.Insert



i = 1
End If
' End If

Next iRow

End With

End Sub

 

[JLGWhiz]

You might need to put up a sample of your data base again so folks will know
what you mean by service group. What you are trying to do is a little
tricky, since the quantiy of rows per service group vary so widely. If they
were all the same, It would be a pretty simple code.

 

[Janis]

sample of my database? this is a spreadsheet. I can give you the header
rows if you want.

The logic of why this works escapes me. It is a hack. It does what I want
for the default number of 40. It is really tricky for me at least. I tried
to backup up to the rows I entered and then add half the amount again. very
hard.

It is connections for servers and they go in pairs. we are adding new
connections half after each service group.

tia,

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 Long

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


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

' If ((SvcGrpNum - i) / 2) And 1 Then
' SvcGrp = .Cells(iRow, ServiceGroupColumn).Value
' MsgBox ("Service Group" & " " & SvcGrp & " " & "has an odd
number of rows. Please add a row and start over.")
' Else

rowsToAdd = (SvcGrpNum - i) / 2


.Cells(iRow, ServiceGroupColumn).Resize(rowsToAdd /
2).EntireRow.Insert
Set rng = .Cells(iRow, ServiceGroupColumn)

rng.Offset(-8, 0).Resize(rowsToAdd).EntireRow.Insert

i = 1
End If
' End If

Next iRow

 

[Janis]

P.S. the service group is only column H. It is a type field with in this
sheet 16 rows each for SvcGrp 001, SvcGrp002, SvcGrp 003.... The problem I
am trying to solve very badly is adding rows to each service group to make a
total of 40. Additionally it must be half added to the end of each service
group and half added in the middle. I would really like some help cleaning
this code up. Other sheets have a total of 8 rows to start with or 12 but I
would be happy if it worked for 16.


tia,