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.
Similar Readings:
- How to Insert Multiple Rows After Every Other Row in Excel
- Insert Multiple Rows in Excel (6 Easy Methods)
- How to Insert a Row within a Cell in Excel (3 Simple Ways)
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.
” title=”
Hello Rifat, Thank you very much. I enjoyed your VBA code.
Would you let me fix a couple of bugs by adding 2 lines at the beginning:
When you come from copying and left active the clipboard, it makes a mess. Solution:
Application.CutCopyMode = False
If you are selecting a range but for some reason (you had pressed Enter or Tab) your active cell is not in the first row. The macro don’t execute over the lines previous to active cell:
Selection(1).Activate ‘Activate the first cell in the selected range so it starts from the first row in selection
Don’t need to post this to the public. please, would you send me an email when you find a spare minute
Hello Jorge.F,
Thank you so much for your suggestion. I think you might want the following code incorporating your suggested lines. Moreover, I tried to select the range through an input box which may help you select the range.