This post will guide you how to filter even rows or odd rows from your data in Excel 2013/2016.

1. Filter Even or Odd Rows Using Excel’s Sort & Filter

Assuming that you have a list of data in range A1:C7, and you want to filter out even rows or odd rows from those data in your worksheet. How to do it. You need to use two Helper column, one column used to calculate the row number, and another row used to check if the current row number is even or odd. Just do the following steps:

Step1: insert the first extra column at the end of the column C, and enter the number 1, and then drag the AutoFill handle until to Cell row 7.

Step2: click AutoFill options icon, and choose Fill series. Then each row number will be displayed in each cell of column D.

Step3: insert the second extra column at the end of the column D, and enter the following formula based on the ISEVEN function to check if the current row number is even or odd.  Then drag the Autofill handle down until to Cell E7.

=ISEVEN(D1)

Step4: keep to select column E, and go to Data tab, click Filter command under Sort & Filter group. And a filter icon will add into the Cell E1.

Step5: click filter icon to filter either TRUE or FALSE values.

Note: if you want to filter even values, just only checked TRUE item in the filter box. And if you want to only filter odd values, just checked FALSE item in the filter box.

2. Filter Even or Odd Rows  using VBA Macro

For those craving a more tailored approach, our second method employs VBA wizardry through a macro. Let’s see how you can unleash the power of automation to filter even or odd rows:

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

Step2: In the editor, go to Insert > Module to add a new module.

Step3: Copy and paste the provided VBA code into the module.

Sub FilterEvenOddRows()
    Dim sourceRange As Range
    Dim filterCriteria As String
    
    ' Select the source range
    On Error Resume Next
    Set sourceRange = Application.InputBox("Select the source range", Type:=8)
    On Error GoTo 0
    
    ' Check if a range is selected
    If sourceRange Is Nothing Then
        MsgBox "No range selected. Operation canceled.", vbExclamation
        Exit Sub
    End If
    
    ' Input box to select filter criteria
    filterCriteria = InputBox("Enter filter criteria (Even/Odd)", "Filter Criteria")
    
    ' Check if the input is valid
    If UCase(filterCriteria) <> "EVEN" And UCase(filterCriteria) <> "ODD" Then
        MsgBox "Invalid filter criteria. Please enter 'Even' or 'Odd'.", vbExclamation
        Exit Sub
    End If
    
    ' Loop through each row in the source range
    For Each cell In sourceRange.Columns(1).Cells
        If (cell.Row Mod 2 = 0 And UCase(filterCriteria) = "EVEN") Or (cell.Row Mod 2 <> 0 And UCase(filterCriteria) = "ODD") Then
            cell.EntireRow.Hidden = False
        Else
            cell.EntireRow.Hidden = True
        End If
    Next cell
End Sub

This code loops through each row in the selected range and hides or shows the entire row based on whether it’s an even or odd row.

Step4: Close the VBA editor. Return to your Excel workbook.

Step5: Press Alt + F8 to open the “Macro” dialog. Select “FilterEvenOddRows” from the list of macros. Click “Run” and select the source range when prompted.

Step6: Enter either “Even” or “Odd” (without quotes) as the filter criteria. click “OK“.

Watch as Excel dynamically filters even or odd rows based on your specified criteria within the selected source range.

3. video: Filter Even or Odd Rows

In this video tutorial, we’ll navigate the nuances of filtering even or odd rows within your dataset in Excel. Join us as we unravel two powerful methods—the inherent Sort & Filter feature and the wizardry of VBA macros.