Rifat Hassan
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
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.
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.
- 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 Shift Cells Down option from appeared 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: 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.
- Finally, you will get the number of blank rows inserted equal to your desired number (3 in this example).
Read More: How to Insert Row in Excel ( 5 Methods)
Similar Readings
- How to Insert Blank Row After Every nth Row in Excel (2 Easy Methods)
- How to Insert a Row within a Cell in Excel (3 Simple Ways)
- Cannot Insert Row in Excel (Quick 7 Fixes)
- Macro to Insert Row and Copy Formula in Excel (2 Methods)
- Excel Macro to Add Row to the Bottom of a Table
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.
- 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).
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.
- 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: 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.
