Presently Excel only allows users to insert one row at a time, but what if we could insert multiple rows simultaneously?

This video will outline one way, via Visual Basic for Applications or VBA, to insert multiple rows simultaneously into an Excel worksheet. 

Strategize Financial Modelling – VBA insert multi rows.xlsb

Commencing the Insert Multiple Rows macro

Go to Visual Basic Editor and then insert a module, which will store our macro. This macro will enable us to insert multiple rows simultaneously. Start with a custom header to describe the macro. 

Remember to give your macro a succinct and self-explanatory name. In this example, we will name our macro insertMultiRows. Don’t forget to disable the three Excel applications of Calculation, EnableEditing and ScreenUpdating. It is desirable to narrate each part of the code, which will appear in green because it will improve the macro’s purpose to other users. 

Now it is recommended to declare variables in the code, which in this case is the row count variable – iCountRows. Next, we are set to transcribe the input feature of our macro.

Input feature of the Insert Multiple Rows macro

This will assist users to stipulate the number of rows to insert into the worksheet. You need to be 100% accurate, whilst inputting this InputBox part of the macro. Hence don’t rush this part of the code.

The underline is simply there to separate the length of the syntax, and place the rest of the code on a second line, which is the case here. 

The use of an error handling feature is to safeguard the macro, from erroneous inputs such as negative numbers. This piece of code will cause the macro to end; if a negative number is inputted.

The final piece of the macro will initiate the insertion of multiple rows. Just like the InputBox syntax, this part of the code can cause run-time errors for macro developers, because it is quite detailed. Take your time in writing out this code. 

We can now re-enable the three Excel applications, which were previously disabled, whilst the macro was running.

Testing the Insert Multiple Rows macro

The macro is now ready to try-out. As we can see, it is working correctly and seamlessly. We will validate the macro again in row 21, but this time we will insert 7 additional rows. 

Hence thanks to VBA, we have the capability to insert multiple rows at once.