Excel: Sample Macro to Insert Multiple Rows

June 2017


Microsoft Excel hosts a number of features that enables users to create worksheets completely tailored to their needs. Among these functionalities is the ability to create a macro, which allows users to make calculations and data manipulations without going through repetitive motions.

Insert Multiple Rows into Excel Worksheet

Excel Task

The below tip will enable you to insert multiple rows between existing rows in an Excel spreadsheet. This macro will enable you to insert a set amount of blank rows into your spreadsheet, and can be modified according to your needs.

Before Macro:
Title1      
Title2
Title3
Title4

After Macro:

Title1

Title2

Title3

Title4

Sample Macro

Here is the macro code needed to perform the above task:
Sub test()    
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A2")
Do
Range(r.Offset(1, 0), r.Offset(j, 0)).EntireRow.Insert
Set r = Cells(r.Row + j + 1, 1)
MsgBox r.Address
If r.Offset(1, 0) = "" Then Exit Do
Loop

End Sub

Related


Published by aakai1056. Latest update on November 6, 2015 at 08:26 AM by ChristinaCCM.
This document, titled "Excel: Sample Macro to Insert Multiple Rows," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).