How to Insert Blank Row After Every Nth Row in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, you may deal with a dynamic dataset, where you want to put rows or columns after some data. There are no direct built-in features in Excel to do it. But there are some alternative ways to insert blank rows after every nth row in Excel. We have also shown a VBA code to perform the operation. It is a swift and automated way. We also covered the manual method to insert blank rows after the nth row in Excel. So, let’s get started to familiarize ourselves with the process of inserting blank rows after every nth row in Excel. So let’s get started.


Insert Blank Row After Every Nth Row in Excel: 2 Methods

Before analyzing the methods, we need to insert a dataset. Here, we have taken a dataset of the Employee Records of Sunflower Kindergarten. Now, we want to insert rows after every nth row.

Dataset

Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.


1. Using Sort and Filter Feature

While inserting blank rows you can use Excel’s built-in Sort and Filter feature. This feature helps you to insert multiple blank rows after the nth row. But you have to insert a Helper column and input additional rows where you have to input the n number. Follow the below steps for better clarification.

📌 Steps:

  • Firstly, take a new column and insert 1 and 2 sequentially in the first two 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, …}

Insert Helper column to insert blank rows after every nth rows in Excel

  • Eventually, insert the value of n in the next cell of the column. For this example, we have inserted n as 3. Then insert the double of n in the next cell. In this example, it is 6.
  • Consequently, drag down the Fill Handle tool to a few cells downwards.

Using Sort and Filter Feature to insert blank rows after every nth row in Excel

  • Sequentially, select the whole data set (including the “Helper Column” and the extended portion down the end).
  • Now, navigate to the Home tab >> choose Sort & Filter from the Editing group >> pick the Custom Sort tool in the Excel toolbar.

  • Consequently, after clicking on the Custom Sort, the Sort dialog wizard pops out.
  • Moreover, from the Sort by option, select the “Helper Column. From the Sort on option, pick Cell Values, and from the Order option, select Smallest to Largest.
  • Lastly, hit OK.

Sort window to insert blank rows after every nth row in Excel

  • After that, you will find blank rows inserted after every nth row (3rd in this example) in your data set.

Finally, delete the “Helper Column” to make your dataset more presentable.

Inserting blank rows after every nth row in Excel using Sort and Filter feature

Read More: Insert New Row in Excel with Shortcut


2. Incorporating VBA Macros

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 number of rows, but what to do if one blank row is not enough?

You need to insert two, three, or even more blank rows after each 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 piece of VBA code. And you will get a lot more flexibility with this method.

Therefore, let’s see how to create and run the macro to insert blank rows with simple steps.

📌 Steps:

  • In the very beginning, you need to navigate to the Developer tab >> choose Visual Basic.

Incorporating VBA Macros to insert blank rows after every nth row in Excel

  • Subsequently, the dialog box appears. Select the Insert tab >> Module >> Module1.

  • Now, from Module 1 in the General box write up the following 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
End Sub

VBA code to insert blank rows after every nth row in Excel

Code Breakdown:

We have set the rng as Range and CountRow as Integer. Here, we take n and k to enter the row after the nth cell and the number of blank rows respectively. We also insert an InputBox command to enter the n and k values. Then we input the Offset (n+j,0) command, where n+j is for the row and the 0 represents the column number. ActiveCell represents the selected range as we set the range to Selection.

  • At this moment, return to your worksheet and select the whole data set (without the Column Headers).

  • Run this Macro.
  • Eventually, you will get an InputBox asking you to provide the value of n. Enter it. I have entered it as 3. Then click OK.

InputBox to enter row number

  • However, you will get another InputBox asking you to enter the number of blank rows. I have entered it as 2. This means, after each nth row, a series of 2 blank rows will be created. If you want one blank row, enter 1. Then click OK.

Finally, you will find that after each nth row of your data set, a series of blank rows (2 in this example) have been created.

Read More: How to Insert Row Below in Excel


How to Insert Blank Rows Automatically in Excel

You can insert rows automatically in Excel before the selected rows. It is pretty easy to perform the operation, but the problem is that you can only insert one row before the selected row. You can’t insert the nth number of rows there. As it is an automatic process, that’s why we attached it here. To get a better visualization, follow the steps below.

📌 Steps:

  • Firstly, select one specific row of your dataset.
  • Secondly, hold the CTRL key and click on the other cells to add the rows.

  • Eventually, leave the CTRL key and right-click on any selected cell.
  • Sequentially, the Context Menu appears. Choose the Insert command.

Insert Command from the Context Menu to insert blank rows after every nth row in excel

Apparently, one row before the selected rows has been added automatically.


How to Insert Multiple Blank Rows Between Data in Excel

Moreover, you can insert multiple rows in Excel between data. It is a very easy and time-saving task. But you can only insert the number of rows that are selected. For your better understanding, we will explain this fact. Suppose you select two rows; then you can insert only two rows before the selected rows. Follow the below steps.

📌 Steps:

  • In the very beginning, select the rows that you wanted like the image below. For selecting the rows hold the CTRL key and click on the cells.
  • Secondly, press the CTRL, SHIFT, and + (plus) keys together.

How to Insert Multiple Blank Rows Between Data in Excel 

  • Subsequently, the Insert dialog wizard pops out. Choose the Entire row and hit OK.

Inert row window

Finally, multiple blank rows are added before the selected rows, like in the image below.

Read More: Excel Formula to Insert Rows Between Data


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it yourself.

Practice Section


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


Conclusion

That’s all about today’s session. And these are some easy methods to insert a blank row after every nth row in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website, ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.


Related Articles


<< Go Back to Insert Rows | Rows in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

2 Comments
  1. 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.

      Sub Insert_Blank_Rows()
      Dim rng As Range
      Dim CountRow As Integer
      Dim i As Integer
      Dim n As Integer
      Application.CutCopyMode = False
      ExcelTitleId = "Exceldemy"
      Set rng = Application.Selection
      Set rng = Application.InputBox("Select your range", ExcelTitleId, rng.Address, Type:=8)
      n = Int(InputBox("Enter the Value of n: "))
      k = Int(InputBox("Enter the Number of Blank Rows: "))
      CountRow = rng.EntireRow.Count
      Selection(1).Activate
      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo