| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert multiple rows
Hi,
I have a excel workbook that i need to insert a number of rows based on a value in a cell, for example; A1 B1 Bob Smith 5 John Smith 3 I need to insert another 4 rows below bob smith (to give 5 in total) and another 2 rows below John Smith (to make 3 in total), etc..... Is this possible, any ideas????? Thanks in advance |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert multiple rows
Option Explicit
Sub testme() Dim wks As Worksheet Dim iRow As Long Dim HowManyRows As Variant Dim FirstRow As Long Dim LastRow As Long Set wks = Worksheets("Sheet1") With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowManyRows = .Cells(iRow, "B").Value If IsNumeric(HowManyRows) Then 'some minor testing If HowManyRows 1 _ And HowManyRows < 100 Then .Rows(iRow + 1).Resize(HowManyRows - 1).Insert 'do you want the names copied down to the 'inserted rows? '.Cells(iRow + 1, "A").Resize(HowManyRows - 1).Value _ ' = .Cells(iRow, "A").Value End If End If Next iRow End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm sdg8481 wrote: Hi, I have a excel workbook that i need to insert a number of rows based on a value in a cell, for example; A1 B1 Bob Smith 5 John Smith 3 I need to insert another 4 rows below bob smith (to give 5 in total) and another 2 rows below John Smith (to make 3 in total), etc..... Is this possible, any ideas????? Thanks in advance -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert multiple rows
Thats absolutely brilliant. Thank You very much. However, just to be cheeky
is there a away where i can copy all the contents of the original row down into the new row, i see you've done it for column A, but how do i convert that for the whole row? Thanks again "Dave Peterson" wrote: Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim HowManyRows As Variant Dim FirstRow As Long Dim LastRow As Long Set wks = Worksheets("Sheet1") With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowManyRows = .Cells(iRow, "B").Value If IsNumeric(HowManyRows) Then 'some minor testing If HowManyRows 1 _ And HowManyRows < 100 Then .Rows(iRow + 1).Resize(HowManyRows - 1).Insert 'do you want the names copied down to the 'inserted rows? '.Cells(iRow + 1, "A").Resize(HowManyRows - 1).Value _ ' = .Cells(iRow, "A").Value End If End If Next iRow End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm sdg8481 wrote: Hi, I have a excel workbook that i need to insert a number of rows based on a value in a cell, for example; A1 B1 Bob Smith 5 John Smith 3 I need to insert another 4 rows below bob smith (to give 5 in total) and another 2 rows below John Smith (to make 3 in total), etc..... Is this possible, any ideas????? Thanks in advance -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert multiple rows
Maybe...
Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim HowManyRows As Variant Dim FirstRow As Long Dim LastRow As Long Set wks = Worksheets("Sheet1") With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowManyRows = .Cells(iRow, "B").Value If IsNumeric(HowManyRows) Then 'some minor testing If HowManyRows 1 _ And HowManyRows < 100 Then .Rows(iRow + 1).Resize(HowManyRows - 1).Insert .Rows(iRow).Copy _ Destination:=.Cells(iRow + 1, "A") _ .Resize(HowManyRows - 1, 1) End If End If Next iRow End With End Sub sdg8481 wrote: Thats absolutely brilliant. Thank You very much. However, just to be cheeky is there a away where i can copy all the contents of the original row down into the new row, i see you've done it for column A, but how do i convert that for the whole row? Thanks again "Dave Peterson" wrote: Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim HowManyRows As Variant Dim FirstRow As Long Dim LastRow As Long Set wks = Worksheets("Sheet1") With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowManyRows = .Cells(iRow, "B").Value If IsNumeric(HowManyRows) Then 'some minor testing If HowManyRows 1 _ And HowManyRows < 100 Then .Rows(iRow + 1).Resize(HowManyRows - 1).Insert 'do you want the names copied down to the 'inserted rows? '.Cells(iRow + 1, "A").Resize(HowManyRows - 1).Value _ ' = .Cells(iRow, "A").Value End If End If Next iRow End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm sdg8481 wrote: Hi, I have a excel workbook that i need to insert a number of rows based on a value in a cell, for example; A1 B1 Bob Smith 5 John Smith 3 I need to insert another 4 rows below bob smith (to give 5 in total) and another 2 rows below John Smith (to make 3 in total), etc..... Is this possible, any ideas????? Thanks in advance -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert multiple rows
perferct. Thank You
"Dave Peterson" wrote: Maybe... Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim HowManyRows As Variant Dim FirstRow As Long Dim LastRow As Long Set wks = Worksheets("Sheet1") With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowManyRows = .Cells(iRow, "B").Value If IsNumeric(HowManyRows) Then 'some minor testing If HowManyRows 1 _ And HowManyRows < 100 Then .Rows(iRow + 1).Resize(HowManyRows - 1).Insert .Rows(iRow).Copy _ Destination:=.Cells(iRow + 1, "A") _ .Resize(HowManyRows - 1, 1) End If End If Next iRow End With End Sub sdg8481 wrote: Thats absolutely brilliant. Thank You very much. However, just to be cheeky is there a away where i can copy all the contents of the original row down into the new row, i see you've done it for column A, but how do i convert that for the whole row? Thanks again "Dave Peterson" wrote: Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim HowManyRows As Variant Dim FirstRow As Long Dim LastRow As Long Set wks = Worksheets("Sheet1") With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowManyRows = .Cells(iRow, "B").Value If IsNumeric(HowManyRows) Then 'some minor testing If HowManyRows 1 _ And HowManyRows < 100 Then .Rows(iRow + 1).Resize(HowManyRows - 1).Insert 'do you want the names copied down to the 'inserted rows? '.Cells(iRow + 1, "A").Resize(HowManyRows - 1).Value _ ' = .Cells(iRow, "A").Value End If End If Next iRow End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm sdg8481 wrote: Hi, I have a excel workbook that i need to insert a number of rows based on a value in a cell, for example; A1 B1 Bob Smith 5 John Smith 3 I need to insert another 4 rows below bob smith (to give 5 in total) and another 2 rows below John Smith (to make 3 in total), etc..... Is this possible, any ideas????? Thanks in advance -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do insert multiple rows in between multiple lines | Excel Discussion (Misc queries) | |||
| Insert Multiple Rows | Excel Discussion (Misc queries) | |||
| How do you insert rows into multiple sheets in a workbook? | Excel Discussion (Misc queries) | |||
| Insert Multiple rows | Setting up and Configuration of Excel | |||
| Insert Multiple Rows | Excel Discussion (Misc queries) | |||
Hybrid Mode
