+ Reply to Thread
Results 1 to 4 of 4

How do I insert two rows between lines of data

  1. #1
    Registered User
    Join Date
    05-17-2013
    Location
    Seattle, WA.
    MS-Off Ver
    Excel 2007
    Posts
    4

    Question How do I insert two rows between lines of data

    Hi all!

    I am new to a position and the person before me wrote this little two line macro to insert two rows between each line of data. Unfortunately some of the reports can be long and you have highlight each row to execute the macro one at a time.

    Here is the current macro...

    Sub Insert()
    '
    ' Insert Macro
    '
    ' Keyboard Shortcut: Ctrl+d
    '
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    End Sub

    Here is what the data could look like

    200-HALIBUT #N/A 132002 UL HAL FLTCH #1 FRESH 5# RDM WT
    280-SALMON SOCKEYE 31 221177 KS SOCK SAL PTN PBO 6OZ VP 10/3#
    280-SALMON SOCKEYE 32 221177AUS KS SOCK PTN PBO 6OZ VP-AUS 10/3#
    280-SALMON SOCKEYE 33 221177CDN KS SOCK PTN PBO 6OZ VP-CDN 10/3#
    280-SALMON SOCKEYE 34 221177SP KS SOCK PTN PBO 6OZ VP-SP 10/3#
    280-SALMON SOCKEYE 35 221177TI KS SOCK PTN PBO 6OZ VP-TI 10/3#
    280-SALMON SOCKEYE 36 221177UK KS SOCK PTN PBO 6OZ VP-UK 10/3#
    281-SALMON PINK 39 408881 TR AK SAL BURGER 4OZ # COSTCO
    281-SALMON PINK #N/A 410032 TR SAL BURGER 4OZ - TAIWAN 12/3#
    140-COD 5 410143 TR COD FLT O/R 1-3OZ PANKO 10/2.5#
    140-COD 6 411389 TR COD FLT O/R 1-3OZ PANKO-TI 10/2.5#
    347-TILAPIA 46 411711 TR TILAPIA 2.5-3.5OZ PANKO 10/3# CLUB ST
    347-TILAPIA 47 411711SP TR TILAPIA 2.5-3.5OZ PANKO-SP 10/3# CLUB ST
    200-HALIBUT 21 412378 PUBHOUSE HAL O/R BTRD CLUB-CDN 10/1.5#
    281-SALMON PINK 41 412572 TR SAL BURGER 4OZ-UK #

    Here is what I need the data to look like with two rows between each row of data

    05 2013
    Class 4 Ship To Item Description Extended Description 2
    200-HALIBUT #N/A 132002 UL HAL FLTCH #1 FRESH 5# RDM WT


    280-SALMON SOCKEYE 31 221177 KS SOCK SAL PTN PBO 6OZ VP 10/3#


    280-SALMON SOCKEYE 32 221177AUS KS SOCK PTN PBO 6OZ VP-AUS 10/3#


    280-SALMON SOCKEYE 33 221177CDN KS SOCK PTN PBO 6OZ VP-CDN 10/3#
    280-SALMON SOCKEYE 34 221177SP KS SOCK PTN PBO 6OZ VP-SP 10/3#
    280-SALMON SOCKEYE 35 221177TI KS SOCK PTN PBO 6OZ VP-TI 10/3#
    280-SALMON SOCKEYE 36 221177UK KS SOCK PTN PBO 6OZ VP-UK 10/3#
    281-SALMON PINK 39 408881 TR AK SAL BURGER 4OZ # COSTCO

    And here is how I started to do the macro but I know there must be an easier way. E the number of rows change monthly.

    Rows("4:5").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("7:8").Select
    'Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    'Rows("10:11").Select
    'Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    'Rows("13:14).Select
    'Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Thanks!

    Tanya

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,088

    Re: How do I insert two rows between lines of data

    One way:

    Please Login or Register  to view this content.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,088

    Re: How do I insert two rows between lines of data

    Change:

    Please Login or Register  to view this content.

    To:

    Please Login or Register  to view this content.

    If you don't want rows inserted after the header row.


    Regards, TMS

  4. #4
    Registered User
    Join Date
    05-17-2013
    Location
    Seattle, WA.
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: How do I insert two rows between lines of data

    OMG!

    totally worked like a charm! Thank you so much you have no idea how many hours of ctl d interations you have saved me from!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1