Solved Insert x rows based on user input and keep formulas

phil22 February 25, 2017 at 03:14:45

I'm no good with spreadsheet formulas so I'm unable to help you.

Thanks Phil, thats a great help

I sent a note to have this moved to the Office Software forum for a better answer. You have to be a little bit crazy to keep you from going insane.

AlwaysWillingToLearn February 27, 2017 at 03:41:00

macrocity, A couple of questions. 1) Will there be any values in row 8 and below? if not then you don't really need to 'insert' any rows. If yes then I presume these are to be moved down? 2) what values go in columns A to D? This is pretty straight forward just need to know the above. message edited by AlwaysWillingToLearn

Not sure what is happening here. I keep submitting a follow-up but it keeps disappearing and is not showing in the thread. I'll give it one more go!! The intention is that the user will open the spreadsheet and then in cell K6 will state how many rows they required and did a macro button "ADD ROWS" which will add K6-1 additional rows. A8 is a row number so if there are four rows A8=1, A9=2, A10=3 and A11=4. The user then add values to cells B8, C8, D8, E8 (in error I previously stated E8 had a formula but it does not it is an input field) and the rest of the rows The formulas will return figures automatically Hope that makes sense and more importantly this submits correctly.

AlwaysWillingToLearn February 27, 2017 at 07:12:45

Sorry macrocity its still not very clear. You will need to provide an example of how your worksheet looks, include additional row beyond row 8 if they exist, or will exist sometime in the furture, for example if they added a bunch of rows 3 times. use the PRE tags to space out your data, alternatively PM me and i'll send you an email address to where you can send me the workbook.

apologises for that. easier I am sure if I could send you the spreadsheet. How do I PM you?

AlwaysWillingToLearn February 27, 2017 at 11:32:02

If you click on my username, then when my profile opens, click on my username again, at the top in white, you can then PM me

AlwaysWillingToLearn February 28, 2017 at 01:16:24

Macrocity, I have sent you a workbook which I think fulfills your requirement. The code I have written is Private Sub AddRows() Dim NoRows As Long NoRows = Range("L5") For i = 1 To NoRows Range("A8").EntireRow.Insert shift:=xlDown Next i For i = 8 To 15 Range("A" & i) = I-7 Next i Range("F8:J16").FillUp End Sub Let us know how you get on with it.

Excellent. Is there anyway to insert the rows beneath row 8 rather than inserting above?

AlwaysWillingToLearn February 28, 2017 at 03:41:37

So your workbook is as following. A B C D E 8 30 100 1000 200 500 9 30 100 1000 200 500 10 30 100 1000 200 500 11 30 100 1000 200 500 5 30 100 1000 200 500 6 30 100 1000 200 500 7 30 100 1000 200 500 8 30 100 1000 200 500 So are you asking that we insert the new rows after row 8 so your data will look like (assuming we are adding 3 new rows) Line Item B C D E 8 1 100 1000 200 500 X X X 9 2 100 1000 200 500 10 3 100 1000 200 500 11 4 100 1000 200 500 5 5 100 1000 200 500 6 6 100 1000 200 500 7 7 100 1000 200 500 8 8 100 1000 200 500 Or can you please show us using the PRE tags as I have to illustrate what you need, and also what will happen to the 'LINE ITEM' column, do these numbers need to now be corrected so that they follow the correct sequence? The more information and the more clear you are the quicker we can provide a suitable answer.

My apologises, i can see where I have mislead you. What I was hoping to achieve was: Line Item B C D E 8 1 100 1000 200 500 Then after the macro button is hit for say 3 rows we get Line Item B C D E 8 1 100 1000 200 500 9 2 10 3 11 4 and copy the formulas for the relevant cells Hope that makes sense

AlwaysWillingToLearn February 28, 2017 at 08:25:02

✔ Best Answer

Ok easily done but i will probably not be able to do this until Thursday. Still havent understood what will happen to the rows that are being moved down. I will not renumber them in my code as you havent specified that. I will just move them down as is and renumber the newly inserted rows 9, 10, 11 etc

Hopefully the following clarifies Line Item B C D E 8 1 100 1000 200 500 TOTAL 100 Then after the macro button is hit for say 3 rows we get Line Item B C D E 8 1 100 1000 200 500 9 2 10 3 11 4 TOTAL 100

Many thanks AlwaysWillingToLearn, that works perfectly! PS Apologises for late response but I have been ill for a few days