How to Delete Every Other Row In Excel (4 Methods)

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.

Apply the Filter Feature to Delete Every Other Row in Excel


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

Apply the Filter Feature to Delete Every Other Row in Excel

  • Each column header has a filter icon now.

Apply the Filter Feature to Delete Every Other Row in Excel

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.

Apply the Filter Feature to Delete Every Other Row in Excel

  • The Filter command is successful to delete every other row as per our requirement.

Apply the Filter Feature to Delete Every Other Row in Excel

  • 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.

Apply the Filter Feature to Delete Every Other Row in Excel

  • That’s how using the Filter feature you can delete every other row in excel.

Apply the Filter Feature to 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!

Insert a Formula to Delete Every Other Row in Excel

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

Insert a Formula to Delete Every Other Row in Excel

  • Press Enter to get the result.

Insert a Formula to Delete Every Other Row in Excel

  • As the formula is working, apply the formula to the rest of the cells.

Insert a Formula to Delete Every Other Row in Excel

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.

Insert a Formula to Delete Every Other Row in Excel

  • We have added a filter option to every column.

Insert a Formula to Delete Every Other Row in Excel

  • 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.

Insert a Formula to Delete Every Other Row in Excel

  • Only the even rows remained!

Insert a Formula to Delete Every Other Row in Excel

  • Now select then all and right-click on the mouse to open options. Choose Delete Row.

Insert a Formula to Delete Every Other Row in Excel

  • A warning message box appears. OK to confirm.

Insert a Formula to Delete Every Other Row in Excel

  • Our total dataset is blank now. Click on the drop-down icon of the Helper Column. This time select FALSE to restore those rows.

Insert a Formula to Delete Every Other Row in Excel

  • Now we have only ODD rows in our dataset. We are successful to delete every other row in excel.

Insert a Formula 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.

Insert a Formula to Delete Every Other Row in Excel

  • Enter to get the result. Apply the same formula to the rest of the cells.

 

Use a Formula to Delete Every Nth Row in Excel

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.

Use a Formula to Delete Every Nth Row in Excel

  • Our task is complete.

Use a Formula to Delete Every Nth Row in Excel


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.

Run VBA Codes to Delete Every Other Row in Excel

  • Click Insert in the VBA window and from the option select Module to insert one.

Run VBA Codes to Delete Every Other Row in Excel

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

Run VBA Codes to Delete Every Other Row in Excel

  • Click on Run to run the code or press F5 to do the same.

Run VBA Codes to Delete Every Other Row in Excel

Step 3:

  • Select the dataset from the worksheet ($B$4:$D$13)

Run VBA Codes to Delete Every Other Row in Excel

  • Click OK and every second row is deleted from the dataset.

Run VBA Codes to Delete Every Other Row in Excel

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!


Similar Articles to Explore

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo