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
"JLGWhiz" wrote:
> 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" wrote:
>
> > 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
> >
|
Similar Threads
