| Home |
| Search |
| Today's Posts |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Rows
Thanks for that Bob
Regards John "Bob Phillips" wrote: Google it http://tinyurl.com/s4nfg -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "JohnUK" wrote in message ... You guys are absolute geniuses That worked great Many thanks to both of you - Tom & Bob Also one of my posts have disappeared regards adding a value depending on info in another cell using VBA. Any idea if it can be retrieved because I need it? Again many thanks Regards John "Tom Ogilvy" wrote: Rows(iLastRow+1).Resize(NUM_ROWS).Insert -- Regards, Tom Ogilvy "JohnUK" wrote in message ... Hi Bob, It worked great apart from it inserted the rows between the last line of data leaving it at the bottom. Any ideas? Regards John "Bob Phillips" wrote: Try something like Const NUM_ROWS As Long = 20 '<====== change to suit Dim iLastRow As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Rows(iLastRow).Resize(NUM_ROWS).Insert ' you now have a block of empty rows to do what you need with -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "JohnUK" wrote in message ... Sorry - been away from the PC. a) Before the new data comes over, the new rows are inserted between the existing data and the last row of the range, thus forcing the range to grow allowing my next code of the macro to copy/mend the formulas down ready for the new data to be inserted. Sorry not very good at explaining John "Bob Phillips" wrote: John, Just to make sure that I get this right. Are you saying that a) you want to copy the formulae down to these new rows, or b) the existing formulae break if you copy the new data in? If the latter, can you give an example? -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "JohnUK" wrote in message ... Hi Bob, Because my data is within a range and my range has lots of formulas that mend after some rows get added/deleted. The idea is that I import some additional data from a different workbook, but depending on the amount of rows that data ocupies (lets say 20) I then want to increase by that amount within my range on the first workbook before the data is entered. I know I could just increase the range by x amount in the first place manually, but because there are so many formulas I am trying to keep the size of my workbook down. John "Bob Phillips" wrote: There are already more than enough rows below 1000 for what you want. What do you want to do with them? -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "JohnUK" wrote in message ... Hi, Another problem I have: Is there a way that rows can be added to the bottom of data depending on a number given? For example: If my data ended on row 1000 (The data fluctuates) and I want another 50 rows added (the amount of rows added would also fluctuate) Thanks John |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding Rows | Excel Worksheet Functions | |||
| 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 | Excel Programming | |||
Threaded Mode
