Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding some rows in the middle of adding some other rows
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.In sert i = 1 End If ' End If Next iRow End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding some rows in the middle of adding some other rows
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.In sert i = 1 End If ' End If Next iRow End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding some rows in the middle of adding some other rows
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.In sert i = 1 End If ' End If Next iRow End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding some rows in the middle of adding some other rows
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, "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.In sert i = 1 End If ' End If Next iRow End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding rows based on no of rows specified from a given position | Excel Worksheet Functions | |||
Adding Rows | Excel Discussion (Misc queries) | |||
Adding five new rows every 40 rows in a spreadsheet? | Excel Discussion (Misc queries) | |||
Adding Rows offsets to working rows across two worksheets | Setting up and Configuration of Excel | |||
Adding new rows | Excel Discussion (Misc queries) |