Tanjima Hossain
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.
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.
Now, a Find and Replace dialog box will appear.
- Second, in the Find what field enter “!” >> hit the Find All button.
- Third, press the CTRL + A to select all the occurrences of the “!” character >> click Close.
- Fourth, in the Home tab, press the Delete dropdown >> hit the Delete Sheet Rows option.
- 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.
Voila! That is how easy it is to delete every nth row in Excel.
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.
=MOD(E5,3)
Here, E5 is the number, while 3 is the divisor and the remainder will be returned after the number is divided by the divisor.
- Then, click the Down arrow >> tick the box with “0” (zero) >> click OK.
- Now, this displays on the rows with “0” (zero) >> select these rows >> Right-click to open Contextual Menu >> press Delete Row.
- 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.
Boom! The final results should look like the image given below.
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.
=MOD(ROW()-4,3)
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.
- Afterward, follow the steps shown previously to delete every nth row.
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.
=ISEVEN(ROW())
For example, the ISEVEN function will determine whether the row is even or odd and give the output as TRUE and FALSE respectively.
- In turn, follow the steps shown in the prior method to delete the rows.
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.
MOD(ROW(),2)
In this situation, every row number will be divided by 2.
- Eventually, follow the steps shown in method 2 to obtain the results shown in the picture below.
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.
Now, this opens the Visual Basic Editor in a new window.
- From this point, go to the Insert tab >> select 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
Rng.Rows(j).Delete
End If
Next j
End Sub
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.
- Following this, click the Run button.
- Then, enter the B5:D13 range >> hit OK.
Consequently, the final output should appear in the screenshot below.
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.
Conclusion
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
- How to Delete Rows in Excel: 7 Methods
- Delete Selected Rows in Excel(8 Approaches)
- How to Delete Multiple Rows in Excel (3 Methods)
- Use VBA to Delete Empty Rows in Excel
- How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)
- Delete Multiple Rows in Excel with VBA (4 Ways)
- VBA Macro to Delete Row if Cell Contains Value in Excel (2 Methods)
