Insert rows based on the value

    Please kindly help with my question. It's really appreciated!


    What I want is

    based on the value in a cell insert rows underneath.


    only if a cell shows 6, then insert one row underneath

    only if a cell shows 9, then insert two rows underneath

    only if a cell shows 12 then insert three rows underneath

    only if a cell shows 15 then insert four rows underneath

    only if a cell shows 18 then insert five rows underneath


    if the cell shows other number don't insert


    Thanks for anyone can help!

    Hi and Welcome to the Forum :)


    You can test following macro ...


    Code
    1. Sub InsertRows()
    2. Dim x, i As Long, n As Long
    3. i = ActiveCell.Row
    4. x = Application.Match(ActiveCell.Value, Array(6, 9, 12, 15, 18), 0)
    5. If Not IsError(x) Then
    6. n = (ActiveCell.Value / 3) - 1
    7. Rows(i + 1 & ":" & i + n).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    8. End If
    9. End Sub


    Hope this will help ... and that you can adapt it to your situation ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    You are welcome


    You do not mention if you are after an Event Macro or a Standard Macro ...


    Why don't you attach a sample file ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    Edited once, last by Carim ().

    For a standard macro


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    TEST FILE ATTACHED.

    I just want to set up a macro which repeated to test cells in column A and insert rows based on the cell value (6, insert 1rows, 9,insert 2rows), perfectly if it can also copy the detail from the first row and fill it up into the new inserted rows. [attach=1226781]

    Thanks!

    Quote from Mingy99

    Thanks again. It tested out very good except the macro doesn't work on the first cell but works well for the following cells in the column.


    In your test file, your first row is located in row 3 and macro does function for this ' first row ' ...


    What exactly do you call ' the first cell ' ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    Quote from Mingy99

    Oh, I think it's because in my actual file, the value started from A2, but the test file started from A3...

    Strange to post a test file ... with a different structure than the actual working file ...:S


    Below is the modification


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • rory

    Changed the title of the thread from “insert rows based on the value please help” to “Insert rows based on the value”.