Results 1 to 5 of 5
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Macro for Row Inserts (2002)

    I have an 18000 row sheet containing Inventory Codes. I need to insert two rows underneath each code, then copy the inventory code into these two rows. There is one header row. So the code that is in row 2 is copied to the two new blank rows 3 & 4, and so on until the end.
    I presume this can be done by a macro, but my VBA skills are very very limited. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for Row Inserts (2002)

    Is there only one instance of each inventory code, or can a code be repeated a number of times? If a code is repeated, do you want the inserted rows only when the code changes? What column is the inventory code in? The code below assumes that the inventory code is in column A, and that you want the two rows inserted after every row in the sheet.

    <pre>Public Sub InsRows()
    Dim I As Long
    With ActiveSheet.Range("A1")
    For I = ActiveSheet.Range("A65536").End(xlUp).Row - 1 To 1 Step -1
    Range(.Offset(I + 1, 0), .Offset(I + 2)).EntireRow.Insert
    .Offset(I + 1, 0).Value = .Offset(I, 0).Value
    .Offset(I + 2, 0).Value = .Offset(I, 0).Value
    Next I
    End With
    End Sub
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Macro for Row Inserts (2002)

    Hi Legare,
    Just back after being away after having a tooth extraction. I will start testing your code later this morning.
    Sorry I was a little light on details, but the code is repeated once only, each code being on a new row. I am extracting the codes form another dataset so I can put then in any column that I choose, so your example using column A will be fine, If there is a difference I will change the script.
    Once again, thank you. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  4. #4
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Macro for Row Inserts (2002)

    In kiwi vernacular, you beaut Legare. I have tested the macro and it works almost perfectly. The only glitch, which is not critical, is that it leaves the first cell not replicated. But hey, 17,999 codes are replicated as expected, so I can live, yep really live with inserting two rows at the top of the dataset and copying the first code to these.
    Your solution is a real gem and a timesaver Legare, your bloods worth bottling as they say. Many thanks. <img src=/S/clever.gif border=0 alt=clever width=15 height=15> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for Row Inserts (2002)

    Your first message said that there was a header row in the first row, and the code should start with row 2. When I run the code, that it what it does. If there is no header row, then change the code to:

    <pre>Public Sub InsRows()
    Dim I As Long
    With ActiveSheet.Range("A1")
    For I = ActiveSheet.Range("A65536").End(xlUp).Row - 1 To 0 Step -1
    Range(.Offset(I + 1, 0), .Offset(I + 2)).EntireRow.Insert
    .Offset(I + 1, 0).Value = .Offset(I, 0).Value
    .Offset(I + 2, 0).Value = .Offset(I, 0).Value
    Next I
    End With
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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