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.
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.
Read More: How to Delete Rows Using Excel Shortcuts
1. Using Filter Feature to Delete Every Other Row in Excel
The Filter feature in Excel is an amazing tool for deleting or filtering 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 in deleting 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 Multiple Rows in Excel at Once
Similar Readings:
- How to Delete Filtered Rows in Excel
- How to Delete Unused Rows in Excel
- How to Find and Delete Rows in Excel
- How to Delete Blank Rows in Excel
- Delete Row If Cell Contains Specific Values in Excel
2. Inserting ISEVEN and ROW Functions to Delete Every Other Row
You can also take the help of Excel formulas to delete rows. Aside from using the Filter command, you can select every other row by using the ISEVEN or ISODD function with the ROW Function 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. Click on 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 in deleting every other row in Excel.
Read More: How to Delete Selected Rows in Excel
Similar Readings
- How to Delete Row If Cell Is Blank in Excel
- Delete Empty Rows at Bottom in Excel
- How to Delete Rows in Excel without Affecting Formulas
- Delete Infinite Rows in Excel
- How to Delete Rows in Excel That Go on Forever
- How to Delete Multiple Rows in Excel with Condition?
3. Use MOD and ROW Functions 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.
Read More: Delete All Rows Below a Certain Row in Excel
Similar Readings:
- How to Delete All Rows Not Containing Certain Text in Excel
- How to Delete Rows Based on Another List in Excel
- How to Remove Rows Containing Identical Transactions in Excel
- [Fixed!] Not Enough Memory to Delete Rows Error in Excel
- How to Delete Row Using VBA
- How to Delete Row with VBA Macro If Cell Is Blank in Excel
- How to Delete Entire Row Based on Cell Value Using VBA in Excel
4. Applying Excel VBA Codes to Delete Every Other Row
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.
Related Content: How to Delete Rows in Excel with Specific Text
Similar Readings:
- How to Delete Row If Cell Contains Value Using Macro in Excel
- How to Delete Row Using Macro If Cell Contains 0 in Excel
- VBA to Delete Every Other Row in Excel
- How to Delete Multiple Rows with VBA in Excel
- How to Delete Rows in a Range with VBA in Excel
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.
Related Content: How to Remove Highlighted Rows in Excel
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
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!
Related Content: How to Delete Hidden Rows in Excel
Related Articles
- Applying VBA Code to Delete Rows Based on Multiple Cell Value
- Excel VBA to Delete Rows with Specific Data
- How to Delete Selected Rows with Excel VBA
- How to Use VBA to Delete Empty Rows in Excel
- How to Use Macro To Delete Rows Based on Criteria in Excel
- How to Filter and Delete Rows with VBA in Excel
- How to Delete Unfiltered Rows in Excel Using VBA
- How to Delete Hidden Rows in Excel VBA