How to Delete Every Other Row in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

1. Using Filter Feature to Delete Every Other Row in Excel

2. Inserting ISEVEN and ROW Functions to Delete Every Other Row

3. Use MOD and ROW Functions Delete Every Nth Row in Excel

4. Applying Excel VBA Codes to Delete Every Other Row

Things to Remember

Conclusion

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.

Similar Readings:

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.

Similar Readings

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.

Similar Readings:

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:

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.

Delete Every Other Row in Excel.xlsx

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

How to Delete Every Other Row in Excel

1. Using Filter Feature to Delete Every Other Row in Excel

2. Inserting ISEVEN and ROW Functions to Delete Every Other Row

3. Use MOD and ROW Functions Delete Every Nth Row in Excel

4. Applying Excel VBA Codes to Delete Every Other Row

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply