Hi All
I need some help to add rows to a spreadsheet based on the input of the user.
I ask the user to input the number of rows they need to add by inputting the value into a cell k6. Ideally I'd like them to press a nacro button which would the add the specified number of rows below row 8, keeping the same formulas in E8, G8, H8 and I8.
Can anyone assist?
ian
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
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.
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.
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?
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
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 SubLet us know how you get on with it.
Excellent. Is there anyway to insert the rows beneath row 8 rather than inserting above?
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 500Or 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 500Then 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 4and copy the formulas for the relevant cells
Hope that makes sense
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 100Then 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