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.

  • 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?

  • royUK

    Closed the thread.