How to Delete Every nth Row in Excel (Easiest 6 Ways)

If you are looking for the easiest ways to delete every nth row in Excel, then you may find this article helpful. Sometimes it is needed to delete every 2nd, 3rd, or any other number of rows repeatedly in a data table. Doing this thing manually for a large dataset will be time-consuming and a very tedious job. So, let’s introduce some easier methods to do this task quickly.

Download Workbook

6 Ways to Delete Every nth Row in Excel

Here, I have a dataset with some rows, but I don’t want the rows containing Shoe as a Product. This Product is appearing in the table in every 3rd row. Now, I will show some ways to delete these unwanted repeated rows easily below.

dataset

 

Method-1: Deleting Every nth Row Using Special Characters

For deleting every nth (3rd in our case) row you can use some special characters in the newly introduced Delete column.

using special character

Step-1:
➤ In the first three rows of the Delete column type special characters such as *, ?, ! etc. or any other characters according to your choice.

using special character

➤Select the first three cells of the Delete column
➤Drag down the Fill Handle tool.

using special character

Now, the first three characters will appear in the rest cells repeatedly.
You can see that here “!” is repeated in every three rows of the Delete column.

using special character

Step-2:
➤Go to Home Tab>>Editing Dropdown>>Find & Select Dropdown>>Find Option

Find & Select

Find and Replace Dialog Box will appear.
➤Type  “!”  in Find what option.
➤Select Find All

using special character

 Now all of the cells containing  “!” will appear.
➤Select all of these cells by pressing CTRL.

using special character

➤Close this Dialog Box

using special character

Here, all of the cells containing “!”  have been selected.

using special character

➤Go to Home Tab>>Cells Dropdown>>Delete Dropdown>>Delete Sheet Rows Option

using special character

Then the rows containing Shoe will be deleted.

using special character

Result:
After deleting the Delete column you will have the following results.

how to delete every nth row in Excel

Read more: How to Delete Multiple Rows in Excel with Condition

Method-2: Using MOD function to Delete Every nth Row

You can use the MOD function to delete every 3rd row. To do this I have added two columns; Counter and Delete.

using MOD function

Step-1:
➤In the Counter column, enter the serial number of these rows except the heading.

using MOD function

Step-2:
➤Select the first Cell, F5 in the Delete column.
➤Type the following formula

=MOD(E5,3)

Here, E5 is the number, and 3 is the divisor and the remainder will be returned after the number is divided by the divisor.

using MOD function

Step-3:
➤Press ENTER
➤Drag down the Fill Handle

using MOD function

Then the following values will appear in the Delete column. Here, 0 will appear in every 3rd row.

using MOD function

Step-4:
➤Select the data table.
➤ Go to Data Tab>> Sort & Filter Dropdown>>Filter Option

using MOD function

➤Select the indicated sign in the Delete column.

using MOD function

➤Select 0 and press OK.

using MOD function

The following table will appear after filtering by 0.

using MOD function

Step-5:
➤Select the data table.
➤Right-click on your mouse
➤Select Delete Row

using MOD function

The unhidden rows have been deleted.

using MOD function

To unhide the hidden rows you have to unclick the Filter option.
➤ Go to Data Tab>> Sort & Filter Dropdown>>Filter Option

using MOD function

Then the following table will appear.

using MOD function

Result:
After deleting the Counter column and the Delete column you will have the following results.

how to delete every nth row in Excel

Read more: How to Delete Specific Rows in Excel

Method-3: Using MOD and ROW function to Delete Every nth Row

You can use the MOD function and the ROW function to delete every 3rd row. To do this I have added the Delete column.

using MOD and ROW function

Step-1:
➤Select Cell E5
➤Type the following formula

=MOD(ROW()-4,3)

Here, 4 is the row number of the first cell with data minus 1 (5-1=4)
3 is the nth row (here it is 3rd) you want to delete

using MOD and ROW function

Step-2:
➤Press ENTER
➤Drag down the Fill Handle

using MOD and ROW function

After that, the following values will pop up where every 3rd row has a value 0.

using MOD and ROW function

Step-3:
➤Follow Step-4 of Method-2.
So, the Delete column has been filtered by value 0.

using MOD and ROW function

Step-4:
➤Follow Step-5 of Method-2.
Now you can see the hidden values here without the rows containing Shoe.

using MOD and ROW function

Result:
After deleting the Delete column you will have the following results.

using MOD and ROW function

Method-4: Using ISEVEN function to Delete Every Other Row

Suppose, you have alternating rows with Shoe and you want to delete these rows.
You can use the ISEVEN function to delete every other row.

using ISEVEN function

Step-1:
➤Select the Cell E5
➤Type the following formula

=ISEVEN(ROW())

Here, the ISEVEN function will determine whether the row is even or odd and give the output as TRUE and FALSE respectively.

using ISEVEN function

Step-2:
➤Press ENTER
➤Drag down the Fill Handle

using ISEVEN function

You will get the following results then. You will find TRUE for every 2nd row which you want to delete.

using ISEVEN function

Step-3:
➤Follow Step-4 of Method-2. You have to just filter the Delete column by clicking on TRUE instead of 0.
So, the delete column has been filtered by TRUE.

using ISEVEN function

Step-4:
➤Follow Step-5 of Method-2.
Now you can see the hidden values here without the rows containing Shoe.

using ISEVEN function

Result:
After deleting the Delete column you will have the following results.

using ISEVEN function

Read more: How to Delete Every Other Row In Excel

Method-5: Using MOD and ROW function to Delete Every Other Row

Suppose, you have alternating rows with Shoe and you want to delete these rows.
You can use the MOD function and the ROW function to delete every other row.

using MOD and ROW function

Step-1:
➤Select Cell E5
➤Type the following formula

=MOD(ROW(),2)

Here, every row number will be divided by

using MOD and ROW function

Step-2:
➤Press ENTER
➤Drag down the Fill Handle

using MOD and ROW function

After that, the following values will appear where every 2nd row has a value 0.

using MOD and ROW function

Step-3:
➤Follow Step-4 of Method-2.
So, the delete column has been filtered by value 0.

using MOD and ROW function

Step-4:
➤Follow Step-5 of Method-2.
Now you can see the hidden values here without the rows containing Shoe.

using MOD and ROW function

Result:
After deleting the Delete column you will have the following results.

using MOD and ROW function

Method-6: Using VBA Code to Delete Every nth Row

You can use VBA Code for deleting every 3rd row or any number of rows as your wish.

using VBA Code

Step-1:
➤Go to Developer Tab>>Visual Basic

using VBA Code

You can also press ALT+F11

Step-2:
Then Visual Basic Editor will pop up.
➤Go to Insert Tab>>Module

using VBA Code

Then Module-1 will be created.

using VBA Code

Step-3:
➤Write the following code

Sub dlt_each_nth_row()

Dim Rng As Range

Set Rng = Application.InputBox("Select the dataset (Excluding headers)", "Range Selection", Type:=8)

For j = Rng.Rows.Count To 1 Step -3

If j Mod 3 = 0 Then

Rng.Rows(j).Delete

End If

Next j

End Sub

Here, j is the number of rows.

using VBA code

Step-4:
➤Press F5
Then the Range Selection Dialog Box will pop up

using VBA code

➤Select the range without the header.
➤Press OK

using VBA code

After that, every 3rd row will be deleted as below.

using VBA code

Practice Section

For doing practice by yourself we have provided a Practice section like below for each method in each sheet on the right side. Please do it by yourself.

practice

Conclusion

In this article, I tried to cover the easiest ways to delete every nth row in Excel effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.


Further Readings:

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo