| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert 8 Rows
How to I adapt this formula to insert 8 blanks rows? Right now it inserts
only one, and I can't read this stuff! Thank you. Connie Sub InsertRow_At_Change() Dim i As Long Dim colno As String 'Dim colno as Long colno = InputBox("Enter a Column Letter") 'colno = InputBox)"Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert 8 Rows
From the vba HELP index for RESIZE
Resize Property See AlsoApplies ToExampleSpecifics Resizes the specified range. Returns a Range object that represents the resized range. expression.Resize(RowSize, ColumnSize) expression Required. An expression that returns a Range object to be resized. RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same. ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same. Example This example resizes the selection on Sheet1 to extend it by one row and one column. Worksheets("Sheet1").Activate numRows = Selection.Rows.Count numColumns = Selection.Columns.Count Selection.Resize(numRows + 1, numColumns + 1).Select This example assumes that you have a table on Sheet1 that has a header row. The example selects the table, without selecting the header row. The active cell must be somewhere in the table before you run the example. Set tbl = ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select -- Don GuillettMicrosoft MVP ExcelSalesAid "Connie Martin" wrote in message ... How to I adapt this formula to insert 8 blanks rows? Right now it inserts only one, and I can't read this stuff! Thank you. Connie Sub InsertRow_At_Change() Dim i As Long Dim colno As String 'Dim colno as Long colno = InputBox("Enter a Column Letter") 'colno = InputBox)"Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert 8 Rows
I'm sorry, Don, but you lost me!
"Don Guillett" wrote: From the vba HELP index for RESIZE Resize Property See AlsoApplies ToExampleSpecifics Resizes the specified range. Returns a Range object that represents the resized range. expression.Resize(RowSize, ColumnSize) expression Required. An expression that returns a Range object to be resized. RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same. ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same. Example This example resizes the selection on Sheet1 to extend it by one row and one column. Worksheets("Sheet1").Activate numRows = Selection.Rows.Count numColumns = Selection.Columns.Count Selection.Resize(numRows + 1, numColumns + 1).Select This example assumes that you have a table on Sheet1 that has a header row. The example selects the table, without selecting the header row. The active cell must be somewhere in the table before you run the example. Set tbl = ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select -- Don GuillettMicrosoft MVP ExcelSalesAid "Connie Martin" wrote in message ... How to I adapt this formula to insert 8 blanks rows? Right now it inserts only one, and I can't read this stuff! Thank you. Connie Sub InsertRow_At_Change() Dim i As Long Dim colno As String 'Dim colno as Long colno = InputBox("Enter a Column Letter") 'colno = InputBox)"Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert 8 Rows
I think he posted the wrong thing, the simplest method would just be to copy
the insert line (though some would shoot me for not being more confusing). Sub InsertRow_At_Change() Dim i As Long Dim colno As String 'Dim colno as Long colno = InputBox("Enter a Column Letter") 'colno = InputBox)"Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub -- -John Please rate when your question is answered to help us and others know what is helpful. "Connie Martin" wrote: I'm sorry, Don, but you lost me! "Don Guillett" wrote: From the vba HELP index for RESIZE Resize Property See AlsoApplies ToExampleSpecifics Resizes the specified range. Returns a Range object that represents the resized range. expression.Resize(RowSize, ColumnSize) expression Required. An expression that returns a Range object to be resized. RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same. ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same. Example This example resizes the selection on Sheet1 to extend it by one row and one column. Worksheets("Sheet1").Activate numRows = Selection.Rows.Count numColumns = Selection.Columns.Count Selection.Resize(numRows + 1, numColumns + 1).Select This example assumes that you have a table on Sheet1 that has a header row. The example selects the table, without selecting the header row. The active cell must be somewhere in the table before you run the example. Set tbl = ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select -- Don GuillettMicrosoft MVP ExcelSalesAid "Connie Martin" wrote in message ... How to I adapt this formula to insert 8 blanks rows? Right now it inserts only one, and I can't read this stuff! Thank you. Connie Sub InsertRow_At_Change() Dim i As Long Dim colno As String 'Dim colno as Long colno = InputBox("Enter a Column Letter") 'colno = InputBox)"Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert 8 Rows
I played around with my original formula, and found that all you have to do
is change the part that says "Resize(1, 1)" to "Resize(8, 1)". That's all I was looking for, and I guess I should've dug deeper before posting. Thank you anyways for replying. Your formula works, as well. Thank you. Connie "John Bundy" wrote: I think he posted the wrong thing, the simplest method would just be to copy the insert line (though some would shoot me for not being more confusing). Sub InsertRow_At_Change() Dim i As Long Dim colno As String 'Dim colno as Long colno = InputBox("Enter a Column Letter") 'colno = InputBox)"Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub -- -John Please rate when your question is answered to help us and others know what is helpful. "Connie Martin" wrote: I'm sorry, Don, but you lost me! "Don Guillett" wrote: From the vba HELP index for RESIZE Resize Property See AlsoApplies ToExampleSpecifics Resizes the specified range. Returns a Range object that represents the resized range. expression.Resize(RowSize, ColumnSize) expression Required. An expression that returns a Range object to be resized. RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same. ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same. Example This example resizes the selection on Sheet1 to extend it by one row and one column. Worksheets("Sheet1").Activate numRows = Selection.Rows.Count numColumns = Selection.Columns.Count Selection.Resize(numRows + 1, numColumns + 1).Select This example assumes that you have a table on Sheet1 that has a header row. The example selects the table, without selecting the header row. The active cell must be somewhere in the table before you run the example. Set tbl = ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select -- Don GuillettMicrosoft MVP ExcelSalesAid "Connie Martin" wrote in message ... How to I adapt this formula to insert 8 blanks rows? Right now it inserts only one, and I can't read this stuff! Thank you. Connie Sub InsertRow_At_Change() Dim i As Long Dim colno As String 'Dim colno as Long colno = InputBox("Enter a Column Letter") 'colno = InputBox)"Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert 8 Rows
That's exactly what I told you to do in my first post -- Don Guillett Microsoft MVP Excel SalesAid Software "Connie Martin" wrote in message ... I played around with my original formula, and found that all you have to do is change the part that says "Resize(1, 1)" to "Resize(8, 1)". That's all I was looking for, and I guess I should've dug deeper before posting. Thank you anyways for replying. Your formula works, as well. Thank you. Connie "John Bundy" wrote: I think he posted the wrong thing, the simplest method would just be to copy the insert line (though some would shoot me for not being more confusing). Sub InsertRow_At_Change() Dim i As Long Dim colno As String 'Dim colno as Long colno = InputBox("Enter a Column Letter") 'colno = InputBox)"Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub -- -John Please rate when your question is answered to help us and others know what is helpful. "Connie Martin" wrote: I'm sorry, Don, but you lost me! "Don Guillett" wrote: From the vba HELP index for RESIZE Resize Property See AlsoApplies ToExampleSpecifics Resizes the specified range. Returns a Range object that represents the resized range. expression.Resize(RowSize, ColumnSize) expression Required. An expression that returns a Range object to be resized. RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same. ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same. Example This example resizes the selection on Sheet1 to extend it by one row and one column. Worksheets("Sheet1").Activate numRows = Selection.Rows.Count numColumns = Selection.Columns.Count Selection.Resize(numRows + 1, numColumns + 1).Select This example assumes that you have a table on Sheet1 that has a header row. The example selects the table, without selecting the header row. The active cell must be somewhere in the table before you run the example. Set tbl = ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select -- Don GuillettMicrosoft MVP ExcelSalesAid "Connie Martin" wrote in message ... How to I adapt this formula to insert 8 blanks rows? Right now it inserts only one, and I can't read this stuff! Thank you. Connie Sub InsertRow_At_Change() Dim i As Long Dim colno As String 'Dim colno as Long colno = InputBox("Enter a Column Letter") 'colno = InputBox)"Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert 8 Rows
Sorry, Don, I got lost with all the info.
"Don Guillett" wrote: That's exactly what I told you to do in my first post -- Don Guillett Microsoft MVP Excel SalesAid Software "Connie Martin" wrote in message ... I played around with my original formula, and found that all you have to do is change the part that says "Resize(1, 1)" to "Resize(8, 1)". That's all I was looking for, and I guess I should've dug deeper before posting. Thank you anyways for replying. Your formula works, as well. Thank you. Connie "John Bundy" wrote: I think he posted the wrong thing, the simplest method would just be to copy the insert line (though some would shoot me for not being more confusing). Sub InsertRow_At_Change() Dim i As Long Dim colno As String 'Dim colno as Long colno = InputBox("Enter a Column Letter") 'colno = InputBox)"Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub -- -John Please rate when your question is answered to help us and others know what is helpful. "Connie Martin" wrote: I'm sorry, Don, but you lost me! "Don Guillett" wrote: From the vba HELP index for RESIZE Resize Property See AlsoApplies ToExampleSpecifics Resizes the specified range. Returns a Range object that represents the resized range. expression.Resize(RowSize, ColumnSize) expression Required. An expression that returns a Range object to be resized. RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same. ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same. Example This example resizes the selection on Sheet1 to extend it by one row and one column. Worksheets("Sheet1").Activate numRows = Selection.Rows.Count numColumns = Selection.Columns.Count Selection.Resize(numRows + 1, numColumns + 1).Select This example assumes that you have a table on Sheet1 that has a header row. The example selects the table, without selecting the header row. The active cell must be somewhere in the table before you run the example. Set tbl = ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select -- Don GuillettMicrosoft MVP ExcelSalesAid "Connie Martin" wrote in message ... How to I adapt this formula to insert 8 blanks rows? Right now it inserts only one, and I can't read this stuff! Thank you. Connie Sub InsertRow_At_Change() Dim i As Long Dim colno As String 'Dim colno as Long colno = InputBox("Enter a Column Letter") 'colno = InputBox)"Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert 8 Rows
Bingo! That does it! Thank you. Connie
"Don Guillett" wrote: change Cells(i, colno).Resize(1, 1).EntireRow.Insert to Cells(i, colno).Resize(8, 1).EntireRow.Insert -- Don Guillett Microsoft MVP Excel SalesAid Software "Connie Martin" wrote in message ... I'm sorry, Don, but you lost me! "Don Guillett" wrote: From the vba HELP index for RESIZE Resize Property See AlsoApplies ToExampleSpecifics Resizes the specified range. Returns a Range object that represents the resized range. expression.Resize(RowSize, ColumnSize) expression Required. An expression that returns a Range object to be resized. RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same. ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same. Example This example resizes the selection on Sheet1 to extend it by one row and one column. Worksheets("Sheet1").Activate numRows = Selection.Rows.Count numColumns = Selection.Columns.Count Selection.Resize(numRows + 1, numColumns + 1).Select This example assumes that you have a table on Sheet1 that has a header row. The example selects the table, without selecting the header row. The active cell must be somewhere in the table before you run the example. Set tbl = ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select -- Don GuillettMicrosoft MVP ExcelSalesAid "Connie Martin" wrote in message ... How to I adapt this formula to insert 8 blanks rows? Right now it inserts only one, and I can't read this stuff! Thank you. Connie Sub InsertRow_At_Change() Dim i As Long Dim colno As String 'Dim colno as Long colno = InputBox("Enter a Column Letter") 'colno = InputBox)"Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| insert rows in a worksheet that do not change adjoining rows | Excel Discussion (Misc queries) | |||
| How do i insert blank rows between data that is thousands of rows | Excel Discussion (Misc queries) | |||
| Insert rows: Formats & formulas extended to additonal rows | Excel Worksheet Functions | |||
| How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) | |||
| How do i insert of spacer rows between rows in large spreadsheets | Excel Discussion (Misc queries) | |||
Linear Mode
