在含And的IF语句中忽略空白单元格的VBA脚本优化需求

Got it, the issue with your original script is that it doesn’t account for blank cells in columns C, D, E, or F—right now, if any of those cells are blank, the >=10 comparison might behave unexpectedly (or delete rows you don’t intend to). We need to add checks to ensure all four cells have actual values before verifying they’re all ≥10.

Here’s the updated script that handles blank cells properly:

Sub test()
    Dim i As Long
    ' Traverse from the last row up to avoid skipping rows after deletion
    For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        ' First check that none of C-F are blank, then verify all values are ≥10
        If Not IsEmpty(Cells(i, "C").Value2) And _
           Not IsEmpty(Cells(i, "D").Value2) And _
           Not IsEmpty(Cells(i, "E").Value2) And _
           Not IsEmpty(Cells(i, "F").Value2) And _
           Cells(i, "C").Value2 >= 10 And _
           Cells(i, "D").Value2 >= 10 And _
           Cells(i, "E").Value2 >= 10 And _
           Cells(i, "F").Value2 >= 10 Then
            Rows(i).Delete
        End If
    Next i
End Sub

Key Changes Explained: #

• We added Not IsEmpty(...) checks for each column: this ensures we only evaluate rows where all four cells have real values (not blank). If any cell in C-F is empty, the condition fails, and we skip deleting that row.
• The underscores _ split the long condition across multiple lines for readability—VBA treats this as a single line of code.

Bonus: Handling "Fake Blanks" (Cells with Spaces) #

If your sheet has cells that look blank but contain spaces or invisible characters, replace Not IsEmpty(...) with Len(Trim(Cells(i, "X").Value2)) > 0 to catch those cases. Here’s that version:

Sub testWithFakeBlanks()
    Dim i As Long
    For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If Len(Trim(Cells(i, "C").Value2)) > 0 And _
           Len(Trim(Cells(i, "D").Value2)) > 0 And _
           Len(Trim(Cells(i, "E").Value2)) > 0 And _
           Len(Trim(Cells(i, "F").Value2)) > 0 And _
           Cells(i, "C").Value2 >= 10 And _
           Cells(i, "D").Value2 >= 10 And _
           Cells(i, "E").Value2 >= 10 And _
           Cells(i, "F").Value2 >= 10 Then
            Rows(i).Delete
        End If
    Next i
End Sub

Trim() removes leading/trailing spaces, and Len() checks if there’s any remaining content—this ensures cells with only spaces are treated as "blank" and skipped.

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