+ Reply to Thread
Results 1 to 11 of 11

Macro for inserting X amount of blank rows below

  1. #1
    Registered User
    Join Date
    09-19-2017
    Location
    Nashville, TN
    MS-Off Ver
    2010
    Posts
    9

    Smile Macro for inserting X amount of blank rows below

    Hi all,

    I'm very much a beginner but want to learn! I'm trying to create a macro so I can insert new blank rows in throughout my worksheets. I wanted to be prompted to enter a number, and then for the new lines to appear below the line I have selected. So far, the macro I have been working from is:

    ' Sub InsertRows()
    Dim x As Integer
    x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
    Range(ActiveCell, ActiveCell.Offset(x - 1, 0)).EntireRow.Insert Shift:=xlDown
    End Sub
    '

    There's a glitch I can't figure out, however. It seems to work ok for entering numbers smaller than 10, but if I want more than that, it will only give me a few rows at a time. The most I've tried to add so far was about 300, and it only gave me about 10 rows. I would appreciate any assistance!

    Thanks,
    Heather

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Macro for inserting X amount of blank rows below

    Works OK in my Excel 2007.

    Instead of using an apostrophe to put code tags around your code, select (highlight) your code and click on the # in the little window.

  3. #3
    Registered User
    Join Date
    09-19-2017
    Location
    Nashville, TN
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro for inserting X amount of blank rows below

    I'm in Excel 2010. Do you have any ideas why it wouldn't work every time even for large numbers?

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Macro for inserting X amount of blank rows below

    I was able to insert 500 rows and I'm using Excel 2010. Could you attach a copy of your file?
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Registered User
    Join Date
    09-19-2017
    Location
    Nashville, TN
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro for inserting X amount of blank rows below

    Thank you for the help, but unfortunately I can't attach this document because it's for my work and would be against my employer's policies. I just tried it in a blank workbook and you're correct that it did work with 500 lines, so I will just assume it's something wrong with the massive spreadsheet I've been trying to use it in.

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Macro for inserting X amount of blank rows below

    I'm not sure how massive your spreadsheet is but keep in mind that a sheet in Excel 2010 can hold only 1,048,576 rows.

  7. #7
    Registered User
    Join Date
    09-19-2017
    Location
    Nashville, TN
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro for inserting X amount of blank rows below

    It's not that large at this point, I'm at about 16,000 rows in one sheet (and growing), and there are about 6 sheets with the same amount, with content in about 20 columns each.

    I will be working on one set of data which I narrowed down by filtering to certain criteria. Then what I needed to do was be able to add X amount of new rows so I can add additional data pertaining to that selection. Maybe Excel just doesn't want to fully cooperate with this macro in this sheet when I have multiple filters and formatting applied? It seems to work ok in simpler spreadsheets.

  8. #8
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Re: Macro for inserting X amount of blank rows below

    May sound basic, but.....

    Where did you place the code and are you running the code when you are selecting that worksheet? and not having another workbook or worksheet selected?

  9. #9
    Registered User
    Join Date
    09-19-2017
    Location
    Nashville, TN
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro for inserting X amount of blank rows below

    That's not too basic for me! I have this code in this workbook, and no other workbooks or sheets are selected. I just tried again to insert 10 lines with the macro, and it gave me 8.

    I appreciate everyone's help with this!

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro for inserting X amount of blank rows below

    Perhaps you should create a small sample file(matching your actual scenario)which we may test on...That is the only way you are gonna get a result.
    The code tests fine in both 2007 and 2010 here too...

    IO think it might have something to do with the inserting of rows while your sheet is filtered....lotsa rows hidden I guess...
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  11. #11
    Registered User
    Join Date
    09-19-2017
    Location
    Nashville, TN
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro for inserting X amount of blank rows below

    I created a sample, and then I'm blocked from uploading it at work. I tried! I would if I could!

    I do think it's an issue with the filtering though. I tried to filter a column by a specific number, and where the rows run consecutively, it will work (almost) exactly right. I wanted 10 rows and it gave me 9, but I can work with that! However, where the row numbers jump because of the filter, it will only give me 1 line even though I wanted 10. I cleared the filter to check that row and it's still just 1 additional row.

    It may just be an issue with having filters applied – but if so, is there anything I can do to fix the code? Or is it just not possible?

    Thanks again for helping!

+ 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. Need Macro to match two different columns and inserting blank rows
    By michael.adams2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2017, 04:33 PM
  2. [SOLVED] Inserting a variable amount of Rows
    By BryceVBA in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-22-2017, 11:29 AM
  3. [SOLVED] Counting number of non blank cells and inserting that amount on another sheet
    By cheeze83 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-20-2013, 05:39 AM
  4. Macro to create sub total as also inserting blank rows after each subtotal
    By nilankar in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-09-2013, 07:26 AM
  5. Inserting variable amount rows between rows
    By marko3 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-23-2011, 01:39 AM
  6. Inserting a blank row in between rows
    By KimG in forum Excel General
    Replies: 3
    Last Post: 08-29-2008, 03:28 PM
  7. Inserting Blank rows after every row upto 2500 rows
    By Manju in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-22-2006, 08:00 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