| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Rows
Having some great help from
Tom Ogilvy & Bob Phillips - I need to go a step further with regards to inserting X amount of rows after the last piece of data. The code I have so far: Const NUM_ROWS As Long = 20 Dim iLastRow As Long iLastRow = Cells(Rows.count, "N").End(xlUp).Row Rows(iLastRow + 1).Resize(NUM_ROWS) Because the number of rows would change constantly, I am trying to get the code to look up a range. My poor attempt: Rows = Range("Number").Value Const NUM_ROWS As Long = " & Rows & " Dim iLastRow As Long iLastRow = Cells(Rows.count, "N").End(xlUp).Row Rows(iLastRow + 1).Resize(NUM_ROWS).Insert Can someone put me back on the right path? Regards John |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding Rows
If the named range "Number" contains the number of rows to insert, then:
Dim iLastRow As Long, rws as Long rws = Range("Number").Value iLastRow = Cells(Rows.count, "N").End(xlUp).Row Rows(iLastRow + 1).Resize(rws).Insert -- Regards, Tom Ogilvy "JohnUK" wrote: Having some great help from Tom Ogilvy & Bob Phillips - I need to go a step further with regards to inserting X amount of rows after the last piece of data. The code I have so far: Const NUM_ROWS As Long = 20 Dim iLastRow As Long iLastRow = Cells(Rows.count, "N").End(xlUp).Row Rows(iLastRow + 1).Resize(NUM_ROWS) Because the number of rows would change constantly, I am trying to get the code to look up a range. My poor attempt: Rows = Range("Number").Value Const NUM_ROWS As Long = " & Rows & " Dim iLastRow As Long iLastRow = Cells(Rows.count, "N").End(xlUp).Row Rows(iLastRow + 1).Resize(NUM_ROWS).Insert Can someone put me back on the right path? Regards John |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding Rows
Many thanks Tom - you done it again
Regards John "Tom Ogilvy" wrote: If the named range "Number" contains the number of rows to insert, then: Dim iLastRow As Long, rws as Long rws = Range("Number").Value iLastRow = Cells(Rows.count, "N").End(xlUp).Row Rows(iLastRow + 1).Resize(rws).Insert -- Regards, Tom Ogilvy "JohnUK" wrote: Having some great help from Tom Ogilvy & Bob Phillips - I need to go a step further with regards to inserting X amount of rows after the last piece of data. The code I have so far: Const NUM_ROWS As Long = 20 Dim iLastRow As Long iLastRow = Cells(Rows.count, "N").End(xlUp).Row Rows(iLastRow + 1).Resize(NUM_ROWS) Because the number of rows would change constantly, I am trying to get the code to look up a range. My poor attempt: Rows = Range("Number").Value Const NUM_ROWS As Long = " & Rows & " Dim iLastRow As Long iLastRow = Cells(Rows.count, "N").End(xlUp).Row Rows(iLastRow + 1).Resize(NUM_ROWS).Insert Can someone put me back on the right path? Regards John |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding rows | Excel Discussion (Misc queries) | |||
| Adding rows based on no of rows specified from a given position | Excel Worksheet Functions | |||
| 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 in VBA | Excel Programming | |||
Hybrid Mode
