Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Insert Rows Macro

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    449
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have 3 columns
    Part No Description Qty

    Ineed a macro to go to column (Qty) and insert that no of rows in, -1
    and to copy the data down as well

    Example
    A B C
    01009 Ram 3
    01009 Ram 3
    01009 Ram 3
    01005 Spacer 2
    01005 Spacer 2
    010002 Door 1


    hope this is clear enough

    thank you

  2. #2
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    864
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default

    Something like this:
    Code:
    Sub CopyThem()
        FinalRow = Cells(65536, 3).End(xlUp).Row
        For i = FinalRow To 2 Step -1
            ThisQty = Cells(i, 3).Value
            If ThisQty > 1 Then
                Cells(i + 1, 1).Resize(ThisQty - 1, 1).EntireRow.Insert
                Cells(i, 1).Resize(1, 3).Copy _
                    Destination:=Cells(i + 1, 1).Resize(ThisQty - 1, 3)
            End If
        Next i
        
    End Sub
    Note that the loop starts at the last row and goes up the sheet so that the row insertions don't interfere with the loop.

    Bill
    View a collection of recent Excel articles in the Excel Daily News

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    449
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thank you it is just what I wanted

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •