Excel VBA需求：删除R列含“Bench”的整行（已定义Lrow变量）

Hey there! I’ve run into this exact issue before with Excel VBA’s filter-and-delete behavior—those hidden gotchas can be super frustrating. Let’s walk through two reliable solutions that should fix your problem, using your existing Lrow variable.

Option 1: Reverse Loop (Most Reliable for Small-to-Medium Datasets) #

This method avoids filter-related bugs entirely by working from the bottom up (so deleting rows doesn’t mess up the row numbers as you iterate):

Dim i As Long

' Start from your last data row and move UP to row 2 (assuming row 1 is your header)
For i = Lrow To 2 Step -1
    ' Check if cell in column R contains "Bench" (vbTextCompare makes it case-insensitive)
    If InStr(1, Range("R" & i).Value, "Bench", vbTextCompare) > 0 Then
        Range("R" & i).EntireRow.Delete
    End If
Next i

Why this works: When you delete a row from the bottom up, you don’t skip any rows that shift up after deletion—this eliminates the most common bug with forward-loop deletion.

Option 2: Fixed Filter Method (Faster for Large Datasets) #

If your original filter approach had issues, it’s likely because you didn’t handle edge cases like no matching rows or accidentally deleting the header. Here’s the corrected version:

' Clear any existing filters first to avoid conflicts
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

' Apply filter to column R for cells containing "Bench"
Range("R1:R" & Lrow).AutoFilter Field:=1, Criteria1:="*Bench*", Operator:=xlAnd

' Delete only visible data rows (skip header)
On Error Resume Next ' Prevent error if no matching rows exist
Range("R2:R" & Lrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error GoTo 0 ' Reset error handling

' Turn off the filter when done
ActiveSheet.AutoFilterMode = False

Key fixes from your original approach:

• Clears existing filters first to avoid unexpected behavior
• Skips the header row (row 1) when deleting
• Adds error handling to prevent crashes if there are no rows with "Bench"

Quick Troubleshooting Tip #

If you were seeing partial deletions or errors before, it’s probably because:

• You didn’t reverse the loop when iterating forward
• You deleted the header row by accident
• No error handling for empty filter results

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