+ Reply to Thread
Results 1 to 21 of 21

Cut Insert every Nth row

  1. #1
    Registered User
    Join Date
    07-10-2020
    Location
    France
    MS-Off Ver
    2016
    Posts
    10

    Cut Insert every Nth row

    Hello,

    Please I want to cut an entire row and insert it after the next row.
    But I have a long list, so I think I need a macro for this.

    To clarify this I've attached an image.

    excel.png

    I want to cut the PRICE row, and insert it after DESCRIPTION row.

    Thanks in advance.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Cut Insert every Nth row

    Welcome to the forum.

    You would be better off converting your data into a properly normalised format. What you have shown in your screen-shot is bad data design and will cause you problems with your data analysis later.

    Your format should be:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    1
    Item Name Description Price Colour
    2
    3
    4
    5
    6
    7
    Sheet: Sheet1

    It could be transformed easily with PowerQuery.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-10-2020
    Location
    France
    MS-Off Ver
    2016
    Posts
    10

    Re: Cut Insert every Nth row

    Hello, thanks for your fast reply. In fact this is not my real data, this is just an example to explain what I need. Thank you

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Cut Insert every Nth row

    Of course - but it is still in a very bad format. I am suggesting that you take this opportunity to remodel it.

    EDIT: Just to demonstrate what I mean, I have used PQ in the attached to normalise a sample set of data based on your screen-shot. This is the way that your data ought to be collated - in columns, not in rows. Hope it helps.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    2
    Column1 Column2 Item Name Description Price Colour
    3
    Item A1 A1 Chapeau Grand
    € 12.20
    Rouge
    4
    Price
    12.20
    A2 Chapeau Petit
    € 19.65
    Vert
    5
    Name Chapeau A3 Chapeau Moyen
    € 14.60
    Bleu
    6
    Description Grand
    7
    Colour Rouge
    8
    Item A2
    9
    Price
    19.65
    10
    Name Chapeau
    11
    Description Petit
    12
    Colour Vert
    13
    Item A3
    14
    Price
    14.60
    15
    Name Chapeau
    16
    Description Moyen
    17
    Colour Bleu
    Sheet: Sheet1
    Attached Files Attached Files
    Last edited by AliGW; 07-10-2020 at 04:18 AM.

  5. #5
    Registered User
    Join Date
    07-10-2020
    Location
    France
    MS-Off Ver
    2016
    Posts
    10

    Re: Cut Insert every Nth row

    Madame, I appreciate your will to help me but I'm desperately in need of a macro to do exactly what I stated above. That data is just a dummy data to make it easier for the reader to understand what I meant.

    To summarize:

    CUT every Nth row and insert it on Nth position/row.

    Or in another way:

    Copy every Nth row, insert a blank row every nth line, paste what we have copied in those blank rows, then delete the copied rows.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Cut Insert every Nth row

    Oh, I completely understand what you are asking.

    Don't worry - one of our VBA experts will advise.

  7. #7
    Registered User
    Join Date
    07-10-2020
    Location
    France
    MS-Off Ver
    2016
    Posts
    10

    Re: Cut Insert every Nth row

    Let's take your example:

    I need the items A1, A2, A3 to become A2, A3, A1 the whole row.
    Attachment 686115

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Cut Insert every Nth row

    I have attached a new version of the workbook that also shows PQ rearranging your rows. If this is something you can work with, let me know, and I will talk you through it.

    Excel 2016 (Windows) 32 bit
    E
    F
    7
    Item Attribute
    8
    Item A1
    9
    Name Chapeau
    10
    Description Grand
    11
    Price
    12.2
    12
    Colour Rouge
    13
    Item A2
    14
    Name Chapeau
    15
    Description Petit
    16
    Price
    19.65
    17
    Colour Vert
    18
    Item A3
    19
    Name Chapeau
    20
    Description Moyen
    21
    Price
    14.6
    22
    Colour Bleu
    Sheet: Sheet1
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-10-2020
    Location
    France
    MS-Off Ver
    2016
    Posts
    10

    Re: Cut Insert every Nth row

    wow, nice that's it

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Cut Insert every Nth row

    It's still bad data layout.

  11. #11
    Registered User
    Join Date
    07-10-2020
    Location
    France
    MS-Off Ver
    2016
    Posts
    10

    Re: Cut Insert every Nth row

    please tell me how did you do that?

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Cut Insert every Nth row

    OK - I will write some instructions. Give me a few minutes and I'll be back.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Cut Insert every Nth row

    1. Select all of the data that you have currently in columns A and B.
    2. With the data selected, go to Data | From Table/Range.
    3. In the PowerQuery editor, click on Advanced Editor on the Home ribbon and replace ALL of the code with this:

    Please Login or Register  to view this content.
    4. Click Done.
    5. On the Home ribbon, click Close & Load.

    All done.

  14. #14
    Registered User
    Join Date
    07-10-2020
    Location
    France
    MS-Off Ver
    2016
    Posts
    10

    Re: Cut Insert every Nth row

    thanks for your efforts, unfortunately it didn't work for me, an empty sheet opens and PQ. Error: Download failed.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Cut Insert every Nth row

    OK - well, unfortunately you have done something wrong.

  16. #16
    Registered User
    Join Date
    07-10-2020
    Location
    France
    MS-Off Ver
    2016
    Posts
    10

    Re: Cut Insert every Nth row

    Any other solution please? with VBA for example..
    I followed the steps above in a new sheet with the same data as yours but it didn't work for me.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by robertelo; 07-10-2020 at 07:33 PM.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Cut Insert every Nth row

    You have made two mistakes:

    1. The table that you have defined on sheet 1 has the name Table3 (in Formulas | Name Manager), so you need to change Table1 to Table3 in the very first (source) line of the M code in the query.

    2. The table you have defined is one row too long - you have selected a blank row at the end, and this is messing up the query, so you need to filter this out - I've added the filter step at atep #4.

    M Code:

    Please Login or Register  to view this content.
    I can't help with VBA - sorry.
    Attached Files Attached Files
    Last edited by AliGW; 07-11-2020 at 01:20 AM.

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Cut Insert every Nth row

    Simple vba
    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    07-10-2020
    Location
    France
    MS-Off Ver
    2016
    Posts
    10

    Re: Cut Insert every Nth row

    the Vba worked. Thank you very much both of you

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Cut Insert every Nth row

    The PowerQuery also works - you just need to do it correctly.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  21. #21
    Registered User
    Join Date
    07-10-2020
    Location
    France
    MS-Off Ver
    2016
    Posts
    10

    Re: Cut Insert every Nth row

    Quote Originally Posted by AliGW View Post
    The PowerQuery also works - you just need to do it correctly.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Done thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Vba that moves cells with text, (up and to left, insert row/ insert copied cell)
    By sergi117 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-04-2018, 03:12 PM
  2. Replies: 5
    Last Post: 08-30-2017, 01:52 PM
  3. Macro insert multiple sheet and insert age band based on numbers
    By saravanan1981 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-01-2017, 01:47 PM
  4. [SOLVED] Enable the Insert icon in Developer tab > Controls > Insert in Excel 2010
    By pedapudipradeep in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-03-2014, 12:41 PM
  5. Replies: 6
    Last Post: 10-02-2014, 06:31 AM
  6. [SOLVED] VBA codes to insert the rows and copy the first entire row text and insert sheet
    By PRADEEPB270 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-26-2013, 05:05 AM
  7. Help - Insert method of Range class failed - Selection.Insert Shift:=xlToRight
    By sandy@excelforum in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-04-2012, 11:14 AM

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