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.
Insert Multiple Rows After Every Other Row in Excel: 4 Quick Ways
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.
1. Using Insert Option from the Context Menu
You can use the Insert option from the Excel Context Menu to insert multiple blank 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.
- 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.
- Afterward, right-click on your mouse.
- Further, from the options available, select Insert.
- Later on, select the Shift Cells Down option from the window to add the rows above the selected rows.
- Finally, you will get the number of blank rows inserted equal to your desired number (3 in this example).
Read More: How to Insert a Total Row in Excel
2. Applying Insert Option from Excel Toolbar
You can also use the Insert option from the Excel toolbar to insert 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.
- Finally, you will get the number of blank rows inserted equal to your desired number (3 in this example).
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 You can simply use keyboard shortcuts to insert rows.
Steps:
- Firstly, select the rows as before.
- 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).
4. Incorporating VBA to Insert Multiple Rows After Every Other Row in Excel
All the methods described above work fine. But they can be used to add blank rows below one specified row or over a 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 Excel’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.
- 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.
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).
- Further, open this macro using the keyboard shortcut Alt + F8 and run the 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.
- 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.
- 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.
Read More: Insert Blank Row After Every Nth Row in Excel
Download Practice Workbook
You can download the practice workbook from here.
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
- How to Insert Rows in Excel Automatically
- Cannot Insert Row in Excel
- Excel Fix: Insert Row Option Greyed Out in Excel
- Excel Formula to Insert Rows Between Data
- How to Insert Row Below in Excel
<< Go Back to Insert Rows | Rows in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!