Closed Thread
Results 1 to 8 of 8

Insert rows based on value in cell.

  1. #1
    Registered User
    Join Date
    05-16-2011
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Thumbs up Insert rows based on value in cell.

    New to programming for excel. Want to run a script to add rows beneath each row based on the value in one of the cells in that row and then move to the next row of data and do the same.

    For example:
    Cell E5 = 7, I want to Insert 7 rows beneath row 5, then move to the next row and insert the number of cells equal to the value in column E of that row.

    Not sure how/where to enter the script either. I'm fine with in-cell formulas, and using the record-macro tool, but have little experience writing and running scripts in excel so as detailed as possible would be greatly appreciated.

    Thanks so much
    Last edited by JPEckelman; 05-16-2011 at 04:18 PM.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Insert rows based on value in cell.

    JPEckelman,

    To add a macro to a workbook:
    1. Save a copy of the Excel workbook you want to modify
    2. Always test macros in a copy so that the original is preserved in case the modifications don't go smoothly
    3. Open the copy of the Excel workbook you want to modify
    4. Use the keyboard shortcut ALT+F11 to open the Visual Basic Editor
    5. Insert -> Module
    6. Copy/Paste the code into that area

    To run a macro in a workbook:
    1. In Excel 2007, go to the View tab in the ribbon
    2. On the far right, click Macros
    3. Double-click the desired macro (I named this one InsertRows)

    Please Login or Register  to view this content.


    Hope that helps,
    ~tigeravatar

  3. #3
    Registered User
    Join Date
    05-16-2011
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Smile Re: Insert rows based on value in cell.

    Worked perfectly, thanks a lot I appreciate it!

  4. #4
    Registered User
    Join Date
    05-18-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Insert rows based on value in cell.

    Hi Tigeravatar,

    I used your macro to insert rows and it works fantastically. Is it possible to copy the original row into the inserted rows so that I now have rows of data = to the number used to determine the # of rows inserted??

    Thanks much.

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Insert rows based on value in cell.

    Wow this thread is old. A little over a full year to the day. And it shows my age, lol. I wouldn't even consider posting code that looks like that anymore. I'm glad that it is working for you, but to have it tweaked, please start a new thread for your question instead of posting in a thread of another member (see forum rules #2, link in my sig).
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Registered User
    Join Date
    03-10-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Insert rows based on value in cell.

    Loved it....worked perfectly with a few mods...saved me about 3 hours...thanks

  7. #7
    Registered User
    Join Date
    08-11-2014
    Location
    Khobar
    MS-Off Ver
    2010
    Posts
    1

    Re: Insert rows based on value in cell.

    Thanks.. Saved me almost a days work. Whoever posted this is awesome!!!!!!

  8. #8
    Registered User
    Join Date
    10-09-2014
    Location
    d
    MS-Off Ver
    7
    Posts
    1

    Re: Insert rows based on value in cell.

    It's rare that I hit/found exactly what I've been looking for.

    And that the code work without big modifications and understanding.

    Your code lines works perfectly.

    Thank you.

Closed 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