How to Insert Multiple Rows After Every Other Row in Excel

We have a data set with the Names, Joining Dates, and Salaries of some employees. We’ll insert new rows into the dataset.

dataset

Method 1 – Using the Insert Option from the Context Menu

Steps:

Select the row you want to insert the values in front of. We have selected the second row because we want to insert rows in front of the second row.

selecting a row

Click and drag down to select the number of rows equal to the number of blank rows you want to include. We chose three rows.

selecting multiple rows

Right-click on the selection and choose Insert.

insert option from context menu

Select the Shift Cells Down option to add the rows above the selected rows.

selecting position of new rows

You will get the number of blank rows inserted equal to the number of selected rows.

excel insert multiple rows every other row result using context menu

Read More: How to Insert a Total Row in Excel

Method 2 – Applying the Insert Option from the Excel Toolbar

Steps:

Select the rows as in Method 1.
Go to the Home tab, select Insert, and choose Insert Sheet Rows in the Excel toolbar in the section called Cells.

applying insert option from toolbar

You will get the number of blank rows inserted equal to your desired number (3 in this example).

excel insert multiple rows every other row result using toolbar

Method 3 – Using a Keyboard Shortcut to Insert Multiple Rows After Every Other Row

Steps:

Select the row where you want to insert and as many rows as you need to insert.

utilizing keyboard shortcut

Press Alt + I, then press R. You will get the number of blank rows inserted equal to your desired number (3 in this example).

excel insert multiple rows every other row result utilizing keyboard shortcut

Method 4 – Incorporating VBA to Insert Multiple Rows After Every Other Row in Excel

Steps:

Open a new VBA window by pressing the keyboard shortcut Alt + F11, then open a new module by going to Insert and selecting Module.

opening new module in vba window

Paste the following code into the module:

Code:

Sub Insert_Blank_Rows()
Dim rg As Range
Dim CtRow As Integer
Dim p As Integer
Dim r As Integer
r = Int(InputBox("Enter the Value of r: "))
k = Int(InputBox("Enter the Number of Blank Rows: "))
Set rg = Selection
CtRow = rg.EntireRow.Count
For p = 1 To Int(CtRow / r)
For j = 0 To k - 1
ActiveCell.Offset(r + j, 0).EntireRow.Insert
Next j
ActiveCell.Offset(r + k, 0).Select
Next p
End Sub

This produces a Macro called Insert_Blank_Rows.

typing code

Code Breakdown

We created a subprocedure named Insert_Blank_Rows.

Dim rg As Range

Dim CtRow As Integer

Dim p As Integer

Dim r As Integer

In these parts, we declared some variables.

Afterward,

r = Int(InputBox("Enter the Value of r: "))

k = Int(InputBox("Enter the Number of Blank Rows: "))

these parts take the necessary inputs.

Set rg = Selection

CtRow = rg.EntireRow.Count

For p = 1 To Int(CtRow / r)

For j = 0 To k - 1

ActiveCell.Offset(r + j, 0).EntireRow.Insert

Next j

ActiveCell.Offset(r + k, 0).Select

Next p

these parts create empty rows.

Return to your worksheet.
Select the whole data set (Without the Column Headers).

selecting dataset and opening macro

Press Alt + F8 and run the macro.

running macro

You will get a Message Box asking you to provide the value of r. Here, r is the value of the interval of rows after between which you want to insert the blank rows.
We have entered r as 3.
Click OK.

You will get another Message Box asking you to enter the number of blank rows. We have entered 2 which means that we’ll insert two rows after every third row in the dataset.
Click OK.

entering number of new rows