How to Insert Multiple Rows After Every Other Row in Excel

One of the frequently used tasks that we perform in Excel is to insert multiple rows after every other row. Today I will show in this article how you can insert multiple rows after every other row in Excel.

preview of excel insert multiple rows every other row


Download Practice Workbook

You can download the practice workbook from here.


4 Quick Ways to Insert Multiple Rows After Every Other Row in Excel

Here, we’ve got a data set with the Names, Joining Dates and Salaries of some employees of a company. Today our objective is to enter multiple rows within the dataset.

dataset


1. Using Insert Option from the Context Menu

You can use the Insert option from the Excel Context Menu to enter one or more rows within your data set.

Steps:

  • Firstly, select the row in front of which you want to enter the blank rows.
  • Here, I have selected the second row because I want to insert rows in front of the second row.

selecting a row

  • Then, drag the mouse downward to select the number of rows equal to the number of blank rows you want to include.
  • Here, I have selected three rows because I want to add three blank rows.

selecting multiple rows

  • Afterward, right-click on your mouse.
  • Further, from the options available, select Insert.

insert option from context menu

  • Later on, select Shift Cells Down option from appeared window to add the rows above the selected rows.

selecting position of new rows

  • Finally, 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 context menu

Read More: Macro to Insert Multiple Rows in Excel (6 Methods)


2. Applying Insert Option from Excel Toolbar

You can also use the Insert option from the Excel toolbar to add multiple rows within your data set. Now, let’s follow the steps outlined below to do this.

Steps:

  • Firstly, select the rows as before.
  • Then, go to the Home > Insert > Insert Sheet Rows tool in the Excel toolbar under the section called Cells.

applying insert option from toolbar

  • Finally, 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

Read More: How to Insert Row in Excel ( 5 Methods)


Similar Readings


3. Utilizing Keyboard Shortcut to Insert Multiple Rows After Every Other Row

In case, the methods shown above don’t seem quick enough for you, you can use this method involving some keyboard shortcuts.

Steps:

  • Firstly, select the rows as before.

utilizing keyboard shortcut

  • Then, press ALT + I on your keyboard.
  • Afterward, holding the ALT button, 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

Read More: Shortcuts to Insert New Row in Excel (6 Quick Methods)


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

All the methods described above works fine. But they can be used to enter blank rows only over or below one specified row in the data set. To add blank rows over multiple rows, you have to use the methods multiple times. That can be a bit troublesome sometimes.

In that case, you can use Excle’s VBA Macro feature to include blank rows after a sequential interval of rows.

Steps:

  • Firstly, open a new VBA window by pressing the keyboard shortcut Alt F11, and open a new module by selecting Insert Module.

opening new module in vba window

  • Then, paste the following codes 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
  • Consecutively, it produces a Macro called Insert_Blank_Rows.

typing code

Code Breakdown

  • Firstly, we created a subprocedure named Insert_Blank_Rows.
  • Then,

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.

  • Finally,

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.

  • Afterward, return to your worksheet.
  • Later on, select the whole data set (Without the Column Headers).

selecting dataset and opening macro

  • Further, open this macro using the keyboard shortcut Alt + F8 and run the macro.

running macro

  • Consecutively, 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.
  • Here, I have entered r as 3. Because I want to insert blank rows after every 3 rows.
  • Then, click OK.

inputting number of rows after which new rows will add

  • Again, you will get another Message Box asking you to enter the number of blank rows. I have entered it as 2 which means, after each r number of rows, a series of 2 blank rows will be created.
  • Further, click OK.

entering number of new rows

  • Finally, you will find after each r number of rows of your data set, a series of blank rows (2 in this example) have been created.

excel insert multiple rows every other row result using vba

Read More: VBA to Insert Row in Excel (11 Methods)


Conclusion

Using these methods, you can insert multiple rows after every other row in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo