如何为VBA Find函数添加条件？保留含World的行不删除

Got it, let's tweak your VBA code to match your needs. The original script deletes any row with "Hello" in column J, but we need to make an exception for rows that also include "World"—even if "Hello" is present too.

What Was Off with the Original Code #

Your current Find loop deletes a row as soon as it spots "Hello", without checking if "World" is also in the cell. On top of that, deleting rows while using Find can cause skipped rows because the data range shifts after each deletion. A more reliable approach is to loop from the bottom of your dataset upwards to avoid this issue.

Updated Working Code #

Here's the adjusted code that only deletes rows with "Hello" and no "World":

Sub KeepWorldRows()
    Dim lastRow As Long
    Dim i As Long
    
    ' Get the last used row in column J to avoid empty rows
    lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "J").End(xlUp).Row
    
    ' Loop from the last row up to row 1 (prevents skipping rows after deletion)
    For i = lastRow To 1 Step -1
        With ActiveSheet.Cells(i, "J")
            ' Check if cell has "Hello" AND does NOT have "World"
            If InStr(1, .Value, "Hello", vbTextCompare) > 0 And _
               InStr(1, .Value, "World", vbTextCompare) = 0 Then
                ' Delete the entire row if conditions are met
                .EntireRow.Delete
            End If
        End With
    Next i
End Sub

Breakdown of How It Works #

• lastRow: Finds the bottom of your actual data in column J, so we don't waste time looping through empty rows.
• Backward Loop: By starting at the last row and moving up (Step -1), deleting a row doesn't affect the rows we haven't checked yet—since we're moving upwards, the row numbers below our current position don't shift relative to our loop counter.
• InStr Function: Checks for the target strings in a case-insensitive way (thanks to vbTextCompare):
  • InStr(1, .Value, "Hello", vbTextCompare) > 0: Returns a number greater than 0 if "Hello" is found.
  • InStr(1, .Value, "World", vbTextCompare) = 0: Returns 0 if "World" is not found.
• Delete Condition: We only delete the row if both conditions are true—"Hello" exists in the cell, but "World" does not.

Testing with Your Sample Data #

For your provided column J entries:

1. Hello world → Kept (contains "World")
2. Hello person → Deleted (has "Hello", no "World")
3. Hello everyone → Deleted
4. Hello person → Deleted
5. Hello world → Kept
6. Hello everyone → Deleted
7. Hello person → Deleted
8. Hello world → Kept

This will leave exactly rows 1, 5, and 8 intact, just as you wanted.

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