Goal: Enable users to insert and fill down multiple rows of data simultaneously in a spreadsheet.

This column will outline one approach to insert, and fill down rows of data simultaneously in an Excel worksheet. The following example contains missing rows of imported data in a financial model. This can be evidenced by referring to the second image, which is the workbook containing the source data.

The first worksheet titled “Income Statement – Import” is clearly missing the revenue line items, Volume 330ml Units and Volume 1000ml Units in rows 13 and 14 from the second worksheet. Therefore, we need a macro that will insert multiple rows and fill down the required rows of data (or formulas).

This is partly based on an earlier video demonstrating a macro to insert multiple rows.

The insert rows fill down macro

First we will disable certain Excel properties to improve the macro’s performance and efficiency. Then we need to declare the row count variable. Next the input message box to prompt the user to specify the number of rows to insert.

An error handle will be placed to handle erroneous inputs like zero or negative values, which we clearly want the macro to avoid performing.

This segment of code will insert multiple rows, based on the value stipulated in the Input Box in the above. This part of the code will then allow the macro to fill down the blank rows, which were previously inserted in the above code.

The code is complete. We can now re-enable our Excel properties back to the default settings.

Running the macro

Let’s test the macro, by inserting 2 rows, which are the two rows of missing imported data between rows 16 and 17. We will repeat the macro on the missing Cost of Goods Sold data between rows 23 and 24.

As we can see the macro is correctly inserting and filling down missing data, which ensures our imported data is complete for our financial model. Here is the example workbook, which contains the insert rows fill down macro.