 # INSERT Number of Rows based on Cell Value

• This is my Sample Data:
COL A | COL B | COL C
x | 5 | abc
y | 2 | def
z | 4 | ghi
t | 1 | jkl
s | 15 | mno
e | 6 | pqr

I want to insert as many number of rows as mentioned in COLUMN B IF it is above 1
Can some one please give me the VBA Code for that.

This is what I had done and failed. It did insert the 5 rows for "item x" in COL A, hoever it didn't go to the next item in COL A ("item y")

For the outer "For Loop", I chose the limit as the sum of COL B.

Thanks in Advance!

• Re: INSERT Number of Rows based on Cell Value

Start by the bottom

Triumph without peril brings no glory: Just try

• Re: INSERT Number of Rows based on Cell Value

Thanks a lot my friend, it worked... I am willing to learn. Can you tell me what these lines mean?

Code
1. LastRow = Range("B" & Rows.count).End(xlUp).Row
2. and
3. temp = Range("B" & n)
4. and
5. Rows(n + 1 & ":" & n + temp).Insert Shift:=xlDown

Also, if I want to copy the data in the Empty Rows Added below each item, what can be done to the code?

• Re: INSERT Number of Rows based on Cell Value

The best way to learn in VBA is to use the F8 key to go step by step in the macro code: rows ...
Then see the effect on the excel file and use the Watch option to see the values in the code or place the cursor on the data to watch: Lastrow, temp

Triumph without peril brings no glory: Just try

• Re: INSERT Number of Rows based on Cell Value

Code
1. LastRow = Range("B" & Rows.count).End(xlUp).Row

Determine the number of used rows on column-b starting from the last row in the column

Code
1. temp = Range("B" & n)

Assigns a number value to the variable named "temp", using the cell "B" & n, where n is a variable defining the row number. If n was currently equal to a value of 7, then Range("B" & n resolves to Range("B7"). The variable "Temp" is then equal to whatever value is in cell B7.

Code
1. Rows(n + 1 & ":" & n + temp).Insert Shift:=xlDown

"n" has been explained above, as well as temp. This is now defining where to insert the rows.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: INSERT Number of Rows based on Cell Value

Bravo AAE
you are great

Triumph without peril brings no glory: Just try

• Re: INSERT Number of Rows based on Cell Value

Awesome...thanks to both of you guys, really appreciate it

• Re: INSERT Number of Rows based on Cell Value

Thanks a lot my friend, it worked... I am willing to learn. Can you tell me what these lines mean?

Code
1. LastRow = Range("B" & Rows.count).End(xlUp).Row
2. and
3. temp = Range("B" & n)
4. and
5. Rows(n + 1 & ":" & n + temp).Insert Shift:=xlDown

Also, if I want to copy the data in the Empty Rows Added below each item, what can be done to the code?

Did you get a solution to copying the data into the new rows? I'm having a similar problem. I have 1 line of data per hotel reservation, and I would like to split this to have one row per night of the reservation. Ie a 3 night reservation would have 3 rows. I have made your formula work to create the rows, now I need all the data to be copied to the empty rows, with the one difference being a column called 'stay date', where stay date would equal check in date in row 1, check in date +1 in row 2, and check in date +2 in row 3.

Please could you advise?