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

If you are looking for the easiest ways to delete every nth row in Excel, then you may find this article helpful. Sometimes you may want to delete every 2nd, 3rd, or any other number of rows repeatedly in a data table. Needless to say, 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 Practice Workbook

6 Ways to Delete Every nth Row in Excel

Here, we have a dataset with the “Product”, “Size”, and “Price” rows, but we don’t want the rows containing “Shoe” which appear in the table in every third row. Now, let’s see each method in detail and with the necessary details.

Data set for delete every nth row in excel

Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.

1. Using Special Characters

First of all, for deleting every nth (3rd row in our case) row you can use the help of special characters.

📌 Steps:

  • First, type special characters such as *, ?, ! etc. >> drag the Fill Handle tool to copy them >> go to Find & Select dropdown >> choose the Find option.

Using Special Characters

Now, a Find and Replace dialog box will appear.

  • Second, in the Find what field enter “!” >> hit the Find All button.

Using Find and replace

  • Third, press the CTRL + A to select all the occurrences of the “!” character >> click Close.

Using shortcut to select all

  • Fourth, in the Home tab, press the Delete dropdown >> hit the Delete Sheet Rows option.

deleting every nth row

  • Additionally, hover the mouse cursor over column E >> Left-click to select the entire column >> use the CTRL + – (Minus) keys to remove the “Delete” column.

deleting columns

Voila! That is how easy it is to delete every nth row in Excel.

delete every nth row in excel with special characters

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

2. Utilizing MOD Function

Besides, you can use the MOD function to delete every 3rd row, so let’s see the process in detail.

📌 Steps:

  • Initially, enter the formula in the F5 cell >> drag the Fill Handle tool to populate the cells below.


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

Utilizing MOD Function

  • After that, select the B4:F13 cells >> navigate to the Data tab >> click on Filter.

Using Filter option

  • Then, click the Down arrow >> tick the box with “0” (zero) >> click OK.

Selecting values to filter

  • Now, this displays on the rows with “0” (zero) >> select these rows >> Right-click to open Contextual Menu >> press Delete Row.

Deleting rows

  • At this point, press the Down arrow >> choose Select All option >> click on OK.

  • Moreover, choose columns E and F >> hit the CTRL + – (Minus) keys to delete them.

using shortcut to delete columns

Boom! The final results should look like the image given below.

delete every nth row in excel with MOD function

Read more: How to Delete Specific Rows in Excel

3. Implementing MOD and ROW Functions 

Alternatively, you can combine the MOD and ROW functions to delete every 3rd row in the dataset.

📌 Steps:

  • First of all, proceed to the E5 cell >> enter the following expression.


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

Implementing MOD and ROW Functions

delete every nth row in excel with MOD and ROW Functions

4. Utilizing ISEVEN Function 

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

📌 Steps:

  • First and foremost, jump to the E5 cell >> enter the equation below into the Formula Bar.


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

Utilizing ISEVEN Function

delete every nth row in excel with ISEVEN Function

Read more: How to Delete Every Other Row in Excel

5. Employing MOD and ROW Functions 

Conversely, you can apply the MOD and ROW functions to delete every other row.

📌 Steps:

  • First of all, type the formula shown below in the E5 cell.


In this situation, every row number will be divided by 2.

Employing MOD and ROW Functions

delete every nth row in excel with MOD and ROW Functions

6. Applying VBA Code 

Last but not least, you can use VBA Code for deleting every 3rd row or any number of rows as your wish.

📌 Steps:

  • To begin with, navigate to the Developer tab >> click the Visual Basic button.

Applying VBA Code

Now, this opens the Visual Basic Editor in a new window.

  • From this point, go to the Insert tab >> select Module.

Inserting module

For ease of reference, copy the code from here and paste it into the window as shown below.

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
End If
Next j
End Sub

VBA Code for delete every nth row in excel

Code Breakdown

Now, let’s understand the VBA code used to delete every nth row in Excel.

  • In the first portion, the sub-routine is given a name, here it is dlt_each_nth_row().
  • Next, define the variable Rng as Range and use the Set statement to prompt this range as user input.
  • Later, use a For…Next statement to iterate through the range and the Mod operator to delete every 3rd row.

Code explanation

  • Following this, click the Run button.

Running macro

  • Then, enter the B5:D13 range >> hit OK.

selecting input range

Consequently, the final output should appear in the screenshot below.

delete every nth row in excel with 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 Section


To sum up, we hope this article helps you understand how to delete every nth row in Excel. Now, if you have any queries, please leave a comment below. And, you can read more articles like this on our website ExcelDemy.

Further Readings





Tanjima Hossain

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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features