如何使用Excel VBA的AutoFilter过滤含偶数的行？

Hey there! Let's figure out how to use Excel VBA's AutoFilter to filter out rows containing even numbers (like 2, 4, 6, 8). I'll walk you through straightforward methods, including a built-in shortcut that makes this super easy.

Excel has a hidden gem for this: a built-in dynamic filter constant specifically for even numbers. No messy custom formulas needed—here's a complete working script:

Sub FilterOutEvenRows()
    Dim ws As Worksheet
    Dim dataRange As Range
    
    ' Set your target worksheet (replace "Sheet1" with your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Clear any existing filters first to avoid conflicts
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    ' Define the full data range (starts at A1, auto-detects all connected data)
    Set dataRange = ws.Range("A1").CurrentRegion
    
    ' Apply AutoFilter to target even numbers in column A (adjust Field number for other columns)
    dataRange.AutoFilter Field:=1, Criteria1:=xlFilterEvenNumbers, Operator:=xlFilterDynamic
    
    ' Delete the visible even-number rows (skip header row with Offset)
    On Error Resume Next ' Prevents crash if no even rows exist
    dataRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
    
    ' Clean up by turning off the filter
    ws.AutoFilterMode = False
End Sub

Quick Breakdown of the Code #

• Set ws = ThisWorkbook.Worksheets("Sheet1"): Swap "Sheet1" for your actual worksheet name.
• If ws.AutoFilterMode Then ws.AutoFilterMode = False: Always clear existing filters first—old filters can mess up new ones.
• dataRange = ws.Range("A1").CurrentRegion: Automatically selects your full data block (handy if your data size changes over time).
• dataRange.AutoFilter Field:=1...: The core of the script! xlFilterEvenNumbers tells AutoFilter to target even values, and Field:=1 refers to the first column (use Field:=2 for column B, Field:=3 for C, etc.).
• dataRange.Offset(1,0)...Delete: Skips the header row and deletes all visible even-number rows. The error handling prevents crashes if there are no even rows to delete.

If you don't want to permanently delete rows, just hide the even ones instead. Replace the delete line with this:

' Hide even rows instead of deleting
dataRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Hidden = True

If you'd rather filter to show only odd rows (instead of removing evens), use the xlFilterOddNumbers constant:

Sub KeepOddRowsOnly()
    Dim ws As Worksheet
    Dim dataRange As Range
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    Set dataRange = ws.Range("A1").CurrentRegion
    
    ' Filter for odd numbers instead
    dataRange.AutoFilter Field:=1, Criteria1:=xlFilterOddNumbers, Operator:=xlFilterDynamic
    
    ' Optional: Copy these odd rows to a new sheet
    ' dataRange.SpecialCells(xlCellTypeVisible).Copy ThisWorkbook.Worksheets("Sheet2").Range("A1")
    
    ' Turn off filter if needed
    ' ws.AutoFilterMode = False
End Sub

• Header row check: If your data doesn't have a header, adjust dataRange to start at the first data row (e.g., Range("A2").CurrentRegion) and remove the Offset(1,0) part when deleting/hiding rows.
• Test first!: Deleting rows is permanent (unless you hit Ctrl+Z immediately). Run the script on a copy of your data to verify it works as expected.
• Adjust columns: Always update the Field number to match the column you want to filter.

内容的提问来源于stack exchange，提问作者Kuan