Insert Custom Number Of Rows In Excel With This Simple Macro


The MS Office apps are designed very intelligently and I’m not saying this just because they make my work infinitely easier. I’m saying it because its true. A few days back we did a post on inserting YouTube videos in Office 2013 and noted that the option was absent in MS Excel simply because it wasn’t needed there. This is the type of intelligence I’m talking about; knowing which features are needed in a specific type of app. It makes sure nothing irrelevant makes it into the final offering. That said, I was surprised to find that where Excel allows you to insert more rows in a worksheet, it doesn’t let you select the number of rows you want to add. You have other work arounds for accomplishing this like selecting the number of rows you want add from the existing ones and then using the insert function. Or you could repeat the insert row step for as many rows as you want. It is repetitive but if you’re willing to spare five minutes, this little Macro can take care of the job for you.

Since this requires a Macro, you will need to save your file as a macro enabled Excel file. To do so, click the ‘Save as type’ drop-down in the save as window and select ‘Excel Marco Enabled Workbook (*xlsm).

Next, it’s time to write the Macro. Go to the View tab and click Macros to create a new one. Name it whatever you like and click ‘Create’. The following window will open automatically.


What’s relevant here are the ‘Sub ‘ and ‘End Sub’ lines. The Sub line will contain the name of your macro while the End Sub marks the end of commnads in that Macro. Since you just created it, there should be nothing between those lines like in the screenshot above. Paste the following between these two lines.

Dim CurrentSheet As Object

    ' Loop through all selected sheets.
    For Each CurrentSheet In ActiveWindow.SelectedSheets
        ' Insert 5 rows at top of each sheet.
    Next CurrentSheet

This code will insert five rows from the very top. It is referenced in the code as A1:A5. To edit it to suit your needs, you need to edit this range. The first value serves as a reference to where the rows will be inserted. If you change it to A100, the rows will be inserted after the first ninety-nine rows. After setting the reference point, you need to enter the number of rows.

Row numbers are defined by the cell range A1:A5. This range will insert 5 rows. To find the correct range for your number of rows, for example you would like to insert 25 rows from A99, you will add 25 to the reference point so that it is A99 and minus one cell from it. I.e. A100 + 99 – 1 = A198. You’re adding rows and not columns so only the number will change and not the column letter. The macro needs to be updated each time you have to insert rows enmass but it’s only a small edit and it works really well so it is worth the time spent.

Macro via Microsoft Support