Rifat Hassan
We often have to insert a blank row after every nth row in our data set while working in Excel. Today I’ll show you how to insert a blank row after every nth row in your data set with proper examples and illustrations.
Download Practice Workbook
2 Easy Methods to Insert Blank Row After Every nth Row in Excel
Here we’ve got a data set with the Names, Salaries, and Joining Dates of some employees of a company called Sunflower group.
Today our objective is to insert a blank row after every nth row in the data set.
Let’s say n is 3 for this example.
1. Run Sort & Filter Tool from Excel Toolbar to Insert Blank Row After Every nth Row
Step 1:
➤ Take a new column and insert 1 and 2 in the first 2 cells of the column. I have named the column Helper Column.
➤ Then drag the Fill Handle to the rest of the cells.
➤ It will generate a sequence of the form {1, 2, 3, 4, 5, 6, …}
Step 2:
➤ Insert the value of n in the next cell of the column. For this example, I have inserted n as 3.
➤ Then insert the double of n in the next cell. In this example, it is 6.
➤ Again drag the Fill Handle up to a few cells downward.
➤ It will generate a sequence like {3, 6, 9, …}
Step 3:
➤ Select the whole data set (Including the Column Headers and the extended portion down the end).
➤ Go to the Home > Sort & Filter > Custom Sort tool in the Excel toolbar.
Step 4:
➤ Click on Custom Sort. You will get a dialogue box called Sort.
➤ From the Sort by option, select the Helper Column. From the Sort on option, select Cell Values, and from the Order option, select Smallest to Largest.
Step 5:
➤ Click on OK. You will find blank rows inserted after every nth row (3rd in this example) in your data set.
Step 6:
➤ Delete the Helper Column. It will look more presentable now.
2. Create a Macro Using VBA Code to Insert Blank Row After Every nth Row
The Sort & Filter method described above works, but it is a bit complex. Furthermore, it has a few limitations.
You can insert exactly one blank row after each n number of rows, but what to do in case one blank row is not enough?
You need to insert two, three, or even more blank rows after each n number of rows. Then?
The Sort & Filter method won’t give you flexibility in this regard. Moreover, if the number of n becomes large, like 10, 15, or more, the process becomes more complex.
But you can accomplish the same task using a small VBA code. And you will get a lot more flexibility in this method.
Therefore, let’s see how to create and run the macro to insert blank rows.
Step 1:
➤ Open a new VBA window and insert a new module (Click here to see how to open and insert a new VBA module in Excel).
➤ Insert this code 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.
Step 2:
➤ Return to your worksheet.
➤ Select the whole data set (Without the Column Headers).
Step 3:
➤ Run this Macro (Click here to see how to run a macro in Excel).
➤ You will get an Inputbox asking you to provide the value of n. Enter it. I have entered it as 3. Then click OK.
Step 4:
➤ You will get another Inputbox 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.
➤ If you want one blank row, enter 1. Then click OK.
➤ 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.
Conclusion
Using these methods, you can insert a blank row after every nth row in your data set. Do you know any other method? Or do you have any questions? Feel free to ask us.
