How to delete every other row or every Nth row in Excel

This tutorial shows you how to efficiently delete every other row, or any Nth row, in Microsoft Excel. We'll cover two methods: filtering and VBA code.

There are many scenarios where removing specific rows is necessary. For instance, you might need to retain data from even-numbered weeks while transferring odd-numbered week data to a separate sheet. The core of the process involves selecting the target rows and then deleting them.

Methods:

• Deleting Every Other Row via Filtering
• Deleting Alternate Rows with VBA
• Deleting Every Nth Row

Method 1: Deleting Every Other Row Using Filtering

This method uses a helper column and Excel's filtering capabilities.

1. Helper Column: Add a new column next to your data. Enter =MOD(ROW(),2) in the first cell of this column and drag it down. This formula returns 0 for even rows and 1 for odd rows.

   

2. Filtering: Apply a filter to the helper column (Data > Sort & Filter > Filter). Select either 0 (to delete even rows) or 1 (to delete odd rows).

   

3. Deleting: Select the visible rows (those matching your filter criteria), right-click, and choose "Delete Row."

   

4. Removing Filter: Turn off the filter (click the Filter button again). Delete the helper column.

   

   Your worksheet now contains only the rows you wanted to keep.

   

Tip: To move rows instead of deleting them, copy the filtered rows before deleting them and paste them into a new location.

Method 2: Deleting Alternate Rows with VBA

This VBA macro automates the process:

Sub Delete_Alternate_Rows_Excel()
  Dim SourceRange As Range
  Set SourceRange = Application.Selection
  Set SourceRange = Application.InputBox("Range:", "Select the range", SourceRange.Address, Type:=8)
  If SourceRange.Rows.Count >= 2 Then
    Dim FirstCell As Range
    Dim RowIndex As Integer
    Application.ScreenUpdating = False
    For RowIndex = SourceRange.Rows.Count - (SourceRange.Rows.Count Mod 2) To 1 Step -2
      Set FirstCell = SourceRange.Cells(RowIndex, 1)
      FirstCell.EntireRow.Delete
    Next
    Application.ScreenUpdating = True
  End If
End Sub

1. Open the VBA editor (Alt F11).

2. Insert a module (Insert > Module).

3. Paste the code into the module.

4. Run the macro (F5).

5. Select the data range when prompted.

   

   The macro will delete every other row in the selected range.

   

Method 3: Deleting Every Nth Row

This expands on the filtering method. Use this formula in the helper column:

=MOD(ROW() - *m*, *n*)

Where:

• m is the row number of the first data row minus 1.
• n is the Nth row to delete.

For example, to delete every 3rd row starting from row 2, use =MOD(ROW()-1,3). Then filter for 0 and delete the visible rows.

This allows you to delete any Nth row efficiently. Remember to adjust m based on your starting row.

The above is the detailed content of How to delete every other row or every Nth row in Excel. For more information, please follow other related articles on the PHP Chinese website!