How to Delete Every Other Row in Excel: Easy Guide

Deleting subsequent rows is very easy as you just highlight, right-click then select delete. The challenge comes in when you want to delete every other row. It can take ages to highlight and delete every other row manually. However, there are a few ways you can achieve this efficiently.

In this tutorial, I will show you how to highlight and delete every other row or column in Excel.

How to delete every other row

Assuming you have the following data and want to remain with products only that are shipping in January, which is located in every other row.

Follow these simple steps.

Using helper column

1. Create a helper column next to your last column containing data

2. Type the following formula in the first cell below the Helper column heading =ISEVEN(ROW()).

3. Double-click the fill handle to apply the formula to the remaining cells

4. Highlight or select all the cells containing data, then navigate to the data tab

5. Click on the filter icon

6. Click on the helper column’s filter icon

7. Untick the true option to be left with false only and press ok.

8. After pressing Ok, you will notice that only cells with false on the helper column are showing up.

9. Select/highlight all the cells labeled false

10. Right-click on one of them and click ok

11. Click ok on the dialog box that pops up

12. Excel will delete all the visible cells except for the header column.

13. Click on the filter icon again on the helper column and tick TRUE

As you can see from the image above, we have been left with only the products shipping in January. You can now delete the helper column to remain with the number of rows with data.

Using VBA code to delete every other row in Excel

You can delete every other row or alternate rows using the VBA code.

1. Open the spreadsheet containing data that you want to delete every other row

2. Launch the VBA editor by clicking the shortcut Alt+F11 or Opt+F11 on Mac. Alternatively, you can open the Developer Tab and then click on Visual Basic to open the Editor. (If you cannot see the developer option on your Excel, Follow this guide to activate it)

3. Click on Insert>Module

4. Copy and paste the following visual Basic code

Sub Delete_Every_Other_Row()
    Dim Rng As Range
    Dim i As Integer
    
    On Error Resume Next
    Set Rng = Application.InputBox("Select the Range (Excluding headers)", "Range Selection", Type:=8)
    On Error GoTo 0
    
    If Rng Is Nothing Then
        MsgBox "Invalid range selected or operation canceled.", vbExclamation
        Exit Sub
    End If
    
    For i = Rng.Rows.Count To 1 Step -2
        Rng.Rows(i).Delete
    Next i
    
    MsgBox "Every other row has been deleted.", vbInformation
End Sub

5. Click on the green Icon or Run to run the code

How to delete every nth row

Apart from deleting every other row, you might need to delete every nth row. For instance, you may need to delete every 3^rd, fourth, fifth, and so on. In this scenario, you need to use a formula.

Assuming you have the dataset in the image below

From the data set above, you can see that April comes after every two rows

To delete all the products being shipped in January, you need to use the following formula

=MOD(ROW(),3)=1

1. Create a helper column

2. Enter the following formula on the first row of the helper column

=MOD(ROW(),3)=1

3. Double-click on it to apply it to all the cells.

As you can see from the image above, the above formula indicates true for every third row

4. Highlight the whole dataset, including the helper column

5. Navigate to the data tab and click on the filter icon

6. Click on the filter icon under the helper column and un-tick FALSE

7. Press ok. You will now remain with every third row after the filter, which you can delete comfortably

8. Right-click on any of the visible rows and click delete

9. Click ok on the pop-up window that appears. You will now remain with blank visible rows.

10. Click on the filter Icon on the helper column and tick FALSE, then press ok. This will make all the rows that had been filtered out appear

11. Delete the helper column to remain with clean data

As you can see from the picture above we have deleted every third row containing the Products shipping on January.

Download the practice worksheet below to try out by yourself.

Final Thoughts

That’s how to delete every other row or every nth row in Excel. If you are an advance Excel use you can save time by using the VBA code. Otherwise the formula method also works fine.