Insert row based on cell value

  • My current code works, but there's got to be a shorter version to insert rows based on a cell value. Currently my code works on a series of If statements. If the value in the current cell is "2" then goto the next row and insert one line. If the value is "3" then go to the next row, insert, next row insert etc. I'm currently written up to a value of 10, but the coding is getting longer and longer. Anybody got a shorter loop that I could use.


    Thanks! :thanx:

  • I'm a bit unclear as to exactly where you want the lines inserted, but try the following and adjust as needed.
    [vba]
    Sub InsertSome()
    Dim i As Integer, n As Integer, m As Long
    n = ActiveCell.Value - 1
    m = ActiveCell.Row - 1
    For i = 1 To n
    Rows(m + 2 * i).Insert
    Next i
    End Sub
    [/vba]

  • If I reade the original post correctly, you want the code to insert the number of rows indicated in the cell value (ie if it is 1 then insert 1 row, if 2, then 2 rows, etc)


    If this is the case, then I believe the Derk's code should be modified as follows:


    Code
    Sub InsertSome()
        Dim i As Integer, n As Integer, m As Long
        n = ActiveCell.Value
        m = ActiveCell.Row
        For i = 1 To n
            Rows(m + 1 * i).Insert
        Next i
    End Sub


    This will insert the rows directly below the active cell.

  • :? I didn't explain this well the first time, so I'll try to clarify this time:


    I have XXX rows of customer data. I have the spreadsheet calculate the number of possible customer options based on a simple count of the columns to the right of the current cell. That count value is then pasted a value into the same column.


    What I'm currently doing is running a macro that looks at the value in the current cell and IF it's greater then 1 then insert a row below it. After it inserts the row it goes to the next cell and insert row etc. This worked fine as long as the customer options were low values, but currently I'm up over 40 possible combinations.


    I've modified the current code you guys provided hoping it would do the same thing, but it keeps adding the rows at the first record row instead of the current record row.


    *sigh* Where the heck did I go wrong???? :?


    Original code


    Modified code


  • See if the following works for you.
    [vba]
    Sub TryThis()
    Dim i As Integer, n As Integer, m As Long, currentCell As Range
    Set currentCell = ActiveCell
    Do While Not IsEmpty(currentCell)
    n = currentCell.Value - 1
    m = currentCell.Row
    If n > 0 Then
    Rows(m + 1 & ":" & m + n).Insert
    Set currentCell = currentCell.Offset(n + 1, 0)
    Else
    Set currentCell = currentCell.Offset(1, 0)
    End If
    Loop
    End Sub
    [/vba]

  • WOO HOO!!!! This forum rocks! :rock: I knew there had to be a simplier way to do what I wanted, but apparently I'm having a serious silver moments. :roll: This solution works perfectly.


    THANK YOU, THANK YOU, THANK YOU!!!

    :thanx:

  • Re: Insert row based on cell value




    the third line given by Mr.Derk should be


    n = ActiveCell.column - 1
    instead of
    n = ActiveCell.Value - 1

  • Re: Insert row based on cell value


    hello! I just saw this thread. This is also kind of like the problem I am facing.



    This is my coding. It does not do wat i want. I want to check if the cell value is 6922 or 6921. if yes insert 3 rows if not do nothing. Kindly advice me in this topic.

  • Re: Insert row based on cell value


    Try
    [vba]Sub Arrage_Data()
    Dim CurrentCell As Range
    Dim s As Integer, e As Long, c As Integer
    Range("A4").Select
    Set CurrentCell = ActiveCell
    Do While Not IsEmpty(CurrentCell)
    s = CurrentCell.Value
    e = CurrentCell.Row
    c = CurrentCell.Column
    If s = 6922 Or s = 6921 Then
    Rows(e + 1 & ":" & e + 3).Insert
    Set CurrentCell = CurrentCell.Offset(4, 0)
    Else
    Set CurrentCell = CurrentCell.Offset(1, 0)
    End If
    Loop
    End Sub[/vba]

  • Re: Insert row based on cell value


    One more qns, If I had to copy the autofill the first line to the fourth line, 5th line to 8th line, 9th to 12th line and so on all the way to 1000+ what should i do? I had a codding work out till 20++ then i give up because it will let me to my grave copy and pasting. Please provide me with ideas. Thanks!

  • Re: Insert row based on cell value


    One more qns, If I had to copy the autofill the first line to the fourth line, 5th line to 8th line, 9th to 12th line and so on all the way to 1000+ what should i do? I had a codding work out till 20++ then i give up because it will let me to my grave copy and pasting. Please provide me with ideas. Thanks! Sorry For this extra post. But I dunno how to delete my post. Sorry

  • Re: Insert row based on cell value


    hi guys! I manage to do the code, but there is some error due to range, can anyone help me? This is the result and the error is commented.

  • Re: Insert row based on cell value


    I'm not clear as to what you want. Combining the earlier question with your code, o\perhaps it's something like
    [vba]Sub CopyDown()
    Dim a, b, c, d As Integer

    a = 1
    b = 1
    d = 4

    Do While a < 71

    Range(Cells(a, b), Cells(a, d)).AutoFill Destination:=Range(Cells(a, b), Cells(a + 3, d)), Type:=xlFillCopy

    a = a + 4
    Loop
    End Sub[/vba]
    If this is not what you want, can you be more specific, or better, attach a sample workbook illustrating the before and after situation?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!