Asikul Himel
You may need to delete unwanted rows from your worksheet while you are recording data or compiling data from different resources. Your unwanted rows can be deleted by applying some features or formulas provided by Excel. There is also a VBA code to do the same task. Today in this article, we will discuss those methods to delete every other row in Excel.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
4 Easy Ways to Delete Every Other Row in Excel
Consider a dataset containing some IDs, Names of some sales representatives, and the number of their Products Sold. In this dataset, you need to delete some specific rows or every Nth row to modify the dataset. We will learn some easy methods to do that.
1. Apply the Filter Feature to Delete Every Other Row in Excel
The Filter feature in Excel is an amazing tool to delete or filter every other row. Learn the procedure by following the steps below.
Step 1:
- First, select the column header. From your Home Tab, go to the Editing Ribbon and select Sort & Filter. From the available option, click on
- Each column header has a filter icon now.
Step 2:
- We need to delete those rows that do not have Zero Products Sold.
- To do that, select the Products Sold column and click on the Drop-Down icon in the column.
- Select Zero to keep it and filter out the rest.
- Click on OK to continue.
- The Filter command is successful to delete every other row as per our requirement.
- You can also keep all the rows without zero values and delete rows that have zeros. You just need to open the Filter option and deselect the zero option and select the rest.
- That’s how using the Filter feature you can delete every other row in excel.
Read more: How to Delete Rows in Excel
2. Insert a Formula to Delete Every Other Row in Excel
Aside from using the Filter command, we can use a formula to delete every other row in excel. The ISEVEN Function with The ROW Function is a great formula to delete even or odd rows from your dataset. We’ll discuss the method by using the steps below.
Step 1:
- We need a Helper Column to apply the formula. Make one!
Step 2:
- In Cell E4 of the Helper Column, apply the ISEVEN with the ROW The formula is:
=ISEVEN(ROW())- This formula will return TRUE for all EVEN rows and FALSE for all ODD
- Press Enter to get the result.
- As the formula is working, apply the formula to the rest of the cells.
Step 3:
- Now we will filter out the true or false values. Select your column header, go to Data Tab and click on Filter from Sort & Filter Ribbon to apply the filter option.
- We have added a filter option to every column.
- Suppose we need to delete the even rows. Open the filter option and select TRUE to keep those even rows and filter every other row. OK to proceed.
- Only the even rows remained!
- Now select then all and right-click on the mouse to open options. Choose Delete Row.
- A warning message box appears. OK to confirm.
- Our total dataset is blank now. Click on the drop-down icon of the Helper Column. This time select FALSE to restore those rows.
- Now we have only ODD rows in our dataset. We are successful to delete every other row in excel.
Read more: How to Delete Specific Rows in Excel
3. Use a Formula to Delete Every Nth Row in Excel
Suppose now we want to delete every third row from our dataset. We can use The MOD function with the ROW formula to delete every Nth Row in Excel. Let’s learn!
Step 1:
- In Cell E5, apply the MOD function with the ROW The formula is:
=MOD(ROW(),3)=1- This formula returns TRUE for every third row and FALSE for every other row.
- Enter to get the result. Apply the same formula to the rest of the cells.
Step 2:
- We want to delete every third row here. To do that, add the filter option on your column header, and from the option select FALSE to keep them and delete every other row.
- Our task is complete.
4. Run VBA Codes to Delete Every Other Row in Excel
You can use a VBA code if you need to delete rows quite often. This will allow you to delete every other row with a single click.
Step 1:
- Go to your Developer Tab, and click on Visual Basic to open the VBA Or you can just press Alt+F11 to open it.
- Click Insert in the VBA window and from the option select Module to insert one.
Step 2:
- We will write the code now. We have provided the codes in the following, you can just copy-paste it. This code will delete every second row from your dataset. The code is,
Sub DeleteEveryOtherRow()
'Delete Row
Dim rng As Range
Dim InputRng As Range
xTitleId = "Delete Row Using VBA"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = InputRng.Rows.Count To 1 Step -2
           Set rng = InputRng.Cells(i, 1)
   rng.EntireRow.Delete
Next
Application.ScreenUpdating = True
End Sub
- Click on Run to run the code or press F5 to do the same.
Step 3:
- Select the dataset from the worksheet ($B$4:$D$13)
- Click OK and every second row is deleted from the dataset.
Read more: Delete Multiple Rows in Excel with VBA
Things to Remember
👉 The MOD with the ROW formula will return FALSE to every third row from the main worksheet.
👉 The VBA code provided in this article will delete every second row from the dataset.
Conclusion
We hope this article proves useful to you. Do comment if you have any questions or queries. You can also check out our other articles related to the Excel tasks!
