Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to add 20 rows at half way point & at beginning
I must need a loop or something at the asterisks. Now as it is, my code adds
half of the needed 40 rows (20)each at the beginning of each service group all the way through (or you might say at the end of the preceeding group). That is okay, but I also need it to add one set of (20) rows at the very beginning(which at this case is the end since I'm going in reverse. How can I add the very first 20 rows? The 2nd problem is I need to add the other 20 rows at the half way point in each service group. As you can see with the comments I can't quite get it to get it right. 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 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 rowsToAdd = (SvcGrpNum - i) / 2 .Cells(iRow, ServiceGroupColumn).Resize(rowsToAdd).EntireRow.In sert **I* '.Cells(iRow, ServiceGroupColumn).Offset(i / 2).Resize(rowsToAdd).EntireRow.Insert '.Cells(iRow, ServiceGroupColumn).Offset(-(SvcGrpNum / 2) + rowsToAdd).Resize(rowsToAdd).EntireRow.Insert i = 1 End If Next iRow End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to add 20 rows at half way point & at beginning
I will repost question to clarify.
"Janis" wrote: I must need a loop or something at the asterisks. Now as it is, my code adds half of the needed 40 rows (20)each at the beginning of each service group all the way through (or you might say at the end of the preceeding group). That is okay, but I also need it to add one set of (20) rows at the very beginning(which at this case is the end since I'm going in reverse. How can I add the very first 20 rows? The 2nd problem is I need to add the other 20 rows at the half way point in each service group. As you can see with the comments I can't quite get it to get it right. 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 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 rowsToAdd = (SvcGrpNum - i) / 2 .Cells(iRow, ServiceGroupColumn).Resize(rowsToAdd).EntireRow.In sert **I* '.Cells(iRow, ServiceGroupColumn).Offset(i / 2).Resize(rowsToAdd).EntireRow.Insert '.Cells(iRow, ServiceGroupColumn).Offset(-(SvcGrpNum / 2) + rowsToAdd).Resize(rowsToAdd).EntireRow.Insert i = 1 End If Next iRow End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set end-of-range to #rows from beginning | Charts and Charting in Excel | |||
Position insertion point at beginning of cell when editing | Excel Discussion (Misc queries) | |||
print half of rows on left and other half on right | Excel Discussion (Misc queries) | |||
EntireRow.Delete only removing half of the rows | Excel Programming | |||
vlookup:suddenly doesn't work for half the rows in the same wk.bk | Excel Discussion (Misc queries) |