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.


Download Practice Workbook

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 called sunflower group.

Data Set to Insert Multiple Rows Every Other Row in Excel

Today our objective is to enter multiple rows within the data set.


1. Run Insert Option from the Context Menu to Insert Multiple Rows After Every Other Row

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

Step 1:

➤ 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 Cell to Insert Multiple Rows Every Other Row in Excel

Step 2:

➤ 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 to Insert Multiple Rows Every Other Row in Excel

Step 3:

➤ Right-click on your mouse.

➤ From the options available, select Insert.

Right Click to Insert Multiple Rows Every Other Row in Excel

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

Insert Multiple Rows Every Other Row in Excel

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


2. Run Insert Option from Excel Toolbar to Insert Multiple Rows After Every Other Row in Excel

You can also use the Insert option from the Excel toolbar to add multiple rows within your data set.

Step 1:

➤ Execute steps 1-2 from method 1.

Step 2:

➤ Go to the Home > Insert tool in the Excel toolbar under the section called Cells.

Insert Tool to Insert Multiple Rows Every Other Row in Excel

Step 3:

➤ From the options available with the drop-down menu, select Insert Sheet Rows.

Insert Tool to Insert Multiple Rows Every Other Row in Excel

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

Insert Multiple Rows Every Other Row in Excel

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


Similar Readings


3. Use Keyboard Shortcut to Insert Multiple Rows After Every Other Row in Excel

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

Step 1:

➤ Execute steps 1-2 from method 1.

Step 2:

➤ Press ALT + I on your keyboard. Then holding the ALT button, press R. You will get the number of blank rows inserted equal to your desired number (3 in this example).

Insert Multiple Rows Every Other Row in Excel

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


4. Use VBA code 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 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 VBA codes (Macro) to include blank rows after a sequential interval of rows.

Step 1:

➤ Open a new VBA window and open a new module.

➤ Paste the following codes in the module:

Code:

Sub Insert_Blank_Rows()

Dim rng As Range

Dim CountRow As Integer

Dim i As Integer

Dim n As Integer

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

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

Set rng = Selection

CountRow = rng.EntireRow.Count

For i = 1 To Int(CountRow / n)

    For j = 0 To k - 1

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

    Next j

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

Next i

End Sub

➤ It produces a Macro called Insert_Blank_Rows.

VBA Code to Insert Multiple Rows Every Other Row in Excel

Step 2:

➤ Return to your worksheet.

➤ Select the whole data set (Without the Column Headers).

Selecting Data Set to Insert Multiple Rows Every Other Row in Excel

Step 3:

➤ Run this Macro.

➤ You will get a Message Box asking you to provide the value of n. Here n is the value of the interval of rows after between which you want to insert the blank rows.

➤ Here I have entered n as 3. Because I want to insert blank rows after every 3 rows. Then click OK.

Message Box to Insert Multiple Rows Every Other Row in Excel

Step 4:

➤ You will get another Message Box asking you to enter the number of blank rows. I have entered it as 2.

➤ This means, after each n number of rows, a series of 2 blank rows will be created.

➤ Then click OK.

Message Box to Insert Multiple Rows Every Other Row in Excel

➤ You will find after each n number of rows of your data set, a series of blank rows (2 in this example) have been created.

Insert Multiple Rows After Every Other Row in Excel

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

ExcelDemy
Logo