+ Reply to Thread
Results 1 to 16 of 16

Add Rows between items

  1. #1
    Registered User
    Join Date
    07-21-2004
    MS-Off Ver
    2010
    Posts
    51

    Add Rows between items

    Running Excel 2010. Need help creating a macro this will add (for now) 10 blank rows between each change in item #. Item #'s will be in column B of spreadsheet.
    I've tried several different macros and can't seem to get any of them to work correctly. Eventually I will be adding like 184 rows between each item #, but for now let's just concentrate on 10 blank rows.

    Appreciate any help.

    attached is a clip of what I'm working with . There is a header Row in Row 1 and data starts in Row 2 with the Item #'s in column B.
    Attached Files Attached Files
    Last edited by RSa; 03-04-2016 at 04:22 PM.

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Add Rows between items

    Is this what you are looking for?
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-21-2004
    MS-Off Ver
    2010
    Posts
    51

    Re: Add Rows between items

    Add Rows.xls

    please see new attached. in your previous macro it added one blank row between each item #.
    attached is new spreadsheet with rows 2 & 3 actually having the same item #.
    Macro needs to insert the blank rows between each change in value & add 10 blank rows instead of 1.

    Thanks

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

    Re: Add Rows between items

    See if this is what you want
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-21-2004
    MS-Off Ver
    2010
    Posts
    51

    Re: Add Rows between items

    that added 10 blank rows between all #'s again. didn't recognize that rows 2 & 3 were same item # & don't need blank rows between them, same with rows 6 & 7

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

    Re: Add Rows between items

    Then
    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,066

    Re: Add Rows between items

    Withdrawn. Didn't cover the 10 or the 184.
    Last edited by jomili; 03-04-2016 at 04:50 PM. Reason: Realized it didn't answer the 10

  8. #8
    Registered User
    Join Date
    07-21-2004
    MS-Off Ver
    2010
    Posts
    51

    Re: Add Rows between items

    ok - that worked great. but lets say that rows 1,2,3, & 4 all have the same item #, so the formula can't actually just start in row 3. this could vary each time, could be the same item # in just 1 row or it could be the same for 100 + rows before changing to new a new #.
    Last edited by RSa; 03-04-2016 at 05:07 PM.

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

    Re: Add Rows between items

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

  10. #10
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,066

    Re: Add Rows between items

    RSA,
    try this one.
    Please Login or Register  to view this content.

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

    Re: Add Rows between items

    Don't understand what you are saying...
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-21-2004
    MS-Off Ver
    2010
    Posts
    51

    Re: Add Rows between items

    jomili- this worked great to an extent. It added my desired # of rows between each change it item #. It did however add blank rows just underneath the header row which is in row 1. Don't need blank rows between heard and first item #. Item #'s will always be in column B so can we get rid of the message box asking for which row we want to analyze?

  13. #13
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,066

    Re: Add Rows between items

    We could get rid of the message box, but that would force you to preselect the range of rows to add the extra rows in. Otherwise, without a defined area, the macro will work on the entire sheet, so every row, which will slow it down considerably.

    If you don't want rows added after header, select from Row2 down to the last row for which you want to insert rows.

  14. #14
    Registered User
    Join Date
    07-21-2004
    MS-Off Ver
    2010
    Posts
    51

    Re: Add Rows between items

    I tried selecting from row 2 down to last row & it still added rows after the header

  15. #15
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,066

    Re: Add Rows between items

    Sorry, you're right. The selection thing is intended to determine the last row you want to do blank rows in, not the first. Changed it in the code below (see line in red). Also hardcoded Column B (you'll have to change this if your structure ever changes)
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    07-21-2004
    MS-Off Ver
    2010
    Posts
    51

    Re: Add Rows between items

    It worked - AWESOME - Thanks

+ 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. Items in a column into rows?
    By Bee008 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2015, 08:44 AM
  2. [SOLVED] counting rows that have 2 items not 1
    By JACKBKNIMBLE in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-24-2013, 02:46 PM
  3. [SOLVED] VBA copies rows based on specific items in a column but need to rename items created
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-06-2013, 11:38 PM
  4. HELP! Selecting certain items/rows
    By rg1210 in forum Excel General
    Replies: 1
    Last Post: 10-06-2011, 09:07 PM
  5. Identify Items, Sort those Items, Copy the Items
    By a1981stingray in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2010, 10:50 AM
  6. help adding/filtering same items in rows
    By MAN in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-20-2009, 01:11 PM
  7. count items that appear in a number of rows
    By sjak in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2009, 01:09 PM

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