Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert Variable Number of Rows; With Loop
Insert Variable Number of Rows
I was playing with some VBA code that would permit me to choose an insertion point, lets say A4, and then insert variable numbers of rows, based on the values in a certain column, lets say column A€¦and of course it will start at the insertion point that the user chooses So far, this is what I have: Sub InsertAnyRows() Dim insertNumber As Range Dim insertStart As Range Dim redRng As Range Dim i As Integer Set insertNumber = Application.InputBox _ (Prompt:="Select a point to insert rows.", Title:="Add a row", Type:=8) insertNumber.Select If insertNumber <= 0 Then MsgBox ("Invalid Number Entered") Exit Sub End If Dim lastrow As Integer ActiveCell.Offset(1, 0).Select Selection.EntireRow.Resize(insertNumber).Insert ActiveCell.Offset(1, 0).Select Do For x = i To 1 Set redRng = Range("A1", Range("A10").End(xlUp)) For Each Cell In redRng Selection.EntireRow.Resize(insertNumber).Insert If ActiveCell.Offset(0, 0).Select = "" Then ActiveCell.Offset(1, 0).Select Selection.EntireRow.Resize(insertNumber).Insert End If Next Cell Next x Loop Until ActiveCell.Offset(0, 0).Select = "" End Sub There is some problem with the loop and I just cant figure it out. Please help!!! Thanks so much!! Ryan-- -- RyGuy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert Variable Number of Rows; With Loop
For starters I don't see where I is set to anything, and if I is higher than
1 than you would need for x=i to 1 step -1 -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "ryguy7272" wrote: Insert Variable Number of Rows I was playing with some VBA code that would permit me to choose an insertion point, lets say A4, and then insert variable numbers of rows, based on the values in a certain column, lets say column A€¦and of course it will start at the insertion point that the user chooses So far, this is what I have: Sub InsertAnyRows() Dim insertNumber As Range Dim insertStart As Range Dim redRng As Range Dim i As Integer Set insertNumber = Application.InputBox _ (Prompt:="Select a point to insert rows.", Title:="Add a row", Type:=8) insertNumber.Select If insertNumber <= 0 Then MsgBox ("Invalid Number Entered") Exit Sub End If Dim lastrow As Integer ActiveCell.Offset(1, 0).Select Selection.EntireRow.Resize(insertNumber).Insert ActiveCell.Offset(1, 0).Select Do For x = i To 1 Set redRng = Range("A1", Range("A10").End(xlUp)) For Each Cell In redRng Selection.EntireRow.Resize(insertNumber).Insert If ActiveCell.Offset(0, 0).Select = "" Then ActiveCell.Offset(1, 0).Select Selection.EntireRow.Resize(insertNumber).Insert End If Next Cell Next x Loop Until ActiveCell.Offset(0, 0).Select = "" End Sub There is some problem with the loop and I just cant figure it out. Please help!!! Thanks so much!! Ryan-- -- RyGuy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert Variable Number of Rows; With Loop
I tried replacing:
For x = i To 1 with: For x=i to 1 step -1 This causes Excel to enter a perpetual loop. I think I am close; any other suggestions? Thanks in advance!! Ryan-- -- RyGuy "John Bundy" wrote: For starters I don't see where I is set to anything, and if I is higher than 1 than you would need for x=i to 1 step -1 -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "ryguy7272" wrote: Insert Variable Number of Rows I was playing with some VBA code that would permit me to choose an insertion point, lets say A4, and then insert variable numbers of rows, based on the values in a certain column, lets say column A€¦and of course it will start at the insertion point that the user chooses So far, this is what I have: Sub InsertAnyRows() Dim insertNumber As Range Dim insertStart As Range Dim redRng As Range Dim i As Integer Set insertNumber = Application.InputBox _ (Prompt:="Select a point to insert rows.", Title:="Add a row", Type:=8) insertNumber.Select If insertNumber <= 0 Then MsgBox ("Invalid Number Entered") Exit Sub End If Dim lastrow As Integer ActiveCell.Offset(1, 0).Select Selection.EntireRow.Resize(insertNumber).Insert ActiveCell.Offset(1, 0).Select Do For x = i To 1 Set redRng = Range("A1", Range("A10").End(xlUp)) For Each Cell In redRng Selection.EntireRow.Resize(insertNumber).Insert If ActiveCell.Offset(0, 0).Select = "" Then ActiveCell.Offset(1, 0).Select Selection.EntireRow.Resize(insertNumber).Insert End If Next Cell Next x Loop Until ActiveCell.Offset(0, 0).Select = "" End Sub There is some problem with the loop and I just cant figure it out. Please help!!! Thanks so much!! Ryan-- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table row fields limit vs. number of rows in use for Excel 2 | Excel Discussion (Misc queries) | |||
Insert Next? Or insert a variable number of records...how? | Excel Discussion (Misc queries) | |||
Aling multiple sets of data by header column | Excel Discussion (Misc queries) | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
asking again, macro to insert rows | Excel Worksheet Functions |