Solved Insert x rows based on user input and keep formulas

February 23, 2017 at 12:50:10
Specs: Windows 7
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

See More: Insert x rows based on user input and keep formulas

Report •

✔ Best Answer
February 28, 2017 at 08:25:02
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


#1
February 25, 2017 at 03:14:45
I'm no good with spreadsheet formulas so I'm unable to help you.

Report •

#2
February 26, 2017 at 11:12:59
Thanks Phil, thats a great help

Report •

#3
February 26, 2017 at 21:25:47
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.


Report •

Related Solutions

#4
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


Report •

#5
February 27, 2017 at 06:59:12
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.

Report •

#6
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.


Report •

#7
February 27, 2017 at 11:27:42
apologises for that. easier I am sure if I could send you the spreadsheet. How do I PM you?

Report •

#8
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

Report •

#9
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.


Report •

#10
February 28, 2017 at 01:26:22
Excellent. Is there anyway to insert the rows beneath row 8 rather than inserting above?

Report •

#11
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.


Report •

#12
February 28, 2017 at 05:33:25
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


Report •

#13
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

Report •

#14
February 28, 2017 at 08:35:04
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


Report •

#15
March 9, 2017 at 08:33:54
Many thanks AlwaysWillingToLearn, that works perfectly!
PS Apologises for late response but I have been ill for a few days

Report •

Ask Question