How to Insert Blank Row After Every nth Row in Excel (2 Easy Methods)

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.

Data Set to Insert Blank Row After Every nth Row in Excel

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, …}

Creating Helper Column to Insert Blank Row After Every nth Row in Excel

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, …}

Extending the Helper Column to Insert Blank Row After Every nth Row in Excel

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.

Selecting the Data Set to Insert Blank Row After Every nth Row in Excel

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.

Sort Dialogue Box to Insert Blank Row After Every nth Row in Excel

Step 5:

➤ Click on OK. You will find blank rows inserted after every nth row (3rd in this example) in your data set.

Insert Blank Row After Every nth Row in Excel

Step 6:

➤ Delete the Helper Column. It will look more presentable now.

Insert Blank Row After Every nth Row in Excel


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.

VBA Code to Insert Blank Row After Every nth Row in Excel

Step 2:

➤ Return to your worksheet.

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

Selecting the Data Set to Insert Blank Row After Every nth Row in Excel

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.

Input Box to Insert Blank Row After Every nth Row in Excel

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.

Input Box to Insert Blank Row After Every nth 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.

VBA Used to Insert Blank Row After Every nth Row in Excel


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.


Related Readings

How to Merge Rows in Excel without losing Data (5 Ways)

How to Insert a Row within a Cell in Excel (3 Simple Ways)

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 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