How To Delete Every Other Row In Excel (Quick Guide)

Deleting every other row in Excel can be a valuable technique for managing large datasets, reorganizing data, or preparing data for further analysis. By removing alternate rows, you can streamline your spreadsheet and make it more manageable.

In this guide, we’re going to show you 2 ways to delete every other row. The first is an easy approach to using a filter. The second is a more automated way using a VBA Macro.

Deleting Alternate Rows through Filtering

  1. Open the Excel workbook that contains the data from which you want to delete every other row.
  2. In an empty column next to your data, enter the formula =MOD(ROW(), 2). This formula uses the MOD function to determine if a row number is even or odd. Rows with an even row number will have a result of 0, and rows with an odd row number will have a result of 1.
  1. Drag the fill handle of the formula down to apply it to all the rows in the column. This will populate the entire column with the MOD formula. (Excel may do this automatically for you.)
  2. Select the entire column that contains your data. You can click on the column header to select the entire column.
  3. Go to the Data tab in the Excel ribbon and click on the Filter button. This will add filter dropdowns to the column.
  1. Filter the column with the MOD formula to show only the rows with a value of 1. To do this, click on the filter dropdown in the column with the MOD formula, uncheck the box for the value “0,” and check the box for the value “1.”
  1. Click OK or Apply to apply the filter. This will hide the rows that have been unchecked.
  1. Select all the visible rows (rows with a value of 1 in the MOD column) by clicking and dragging over the row numbers on the left side of the sheet.
  2. Right-click on one of the selected row numbers and choose Delete Row from the context menu. This will delete the selected rows.
  1. Turn off the filter by clicking on the filter dropdown in the column with the MOD formula and selecting Clear Filter or All.
  2. You should now have every other row deleted, and the remaining rows will be displayed.

Note: Since the column with the MOD formula is still active, the column will still show 0s and 1s. However, the original rows are deleted.

Using a VBA Macro for Automatic Row Deletion

Note: Using the macro will permanently delete the data. Make sure to have a backup, as the data is not recoverable.

If you prefer using a VBA macro for automatic row deletion in Excel, you can follow these steps outlined below to efficiently delete every other row from a dataset:

1. Press Alt+F11 to open the Visual Basic for Applications (VBA) editor.

2. Click on “Insert” in the menu, then select “Module” from the dropdown to insert a new module.

3. In the new module, input the following VBA code:

“`

Sub Delete_Every_Other_Row()

    ‘ Dimension variables.

    Dim Y As Boolean

    Dim I As Long

    Dim xRng As Range

    Dim xCounter As Long

    Dim userResponse As VbMsgBoxResult

    ‘ Prompt the user to choose which row to delete first.

    userResponse = MsgBox(“Delete starting from the first row?” & vbCrLf & “Click ‘Yes’ to start deleting from the first row,” & vbCrLf & “and ‘No’ to start deleting from the second row.”, vbYesNo, “Choose Row to Delete First”)

    ‘ Set Y based on user response.

    Y = (userResponse = vbYes)

    I = 1

    Set xRng = Selection

    ‘ Loop once for every row in the selection.

    For xCounter = 1 To xRng.Rows.Count

        ‘ If Y is True, then…

        If Y = True Then

            ‘ …delete an entire row of cells.

            xRng.Cells(I).EntireRow.Delete

        ‘ Otherwise…

        Else

            ‘ …increment I by one so we can cycle through range.

            I = I + 1

        End If

        ‘ If Y is True, make it False; if Y is False, make it True.

        Y = Not Y

    Next xCounter

End Sub

“`

4. Close the VBA editor by clicking the “X” in the top right corner or pressing Alt + Q.

5. Back in the Excel workbook, select the rows that you want to apply the macro to (including the alternate rows you want to delete).

6. Press Alt + F8 to open the “Macro” dialog box.

7. Select the macro “Delete_Every_Other_Row” in the list and click “Run.”

8. The macro will prompt you to either delete the first row or the second row. Choose from either of the options.

9. The selected rows will now be deleted.

The VBA macro will now automatically delete every other row in the selected range of your dataset. It’s an efficient and quick method to address row deletion needs, especially for large or complex datasets.