VBA删除Sheet1中B列空行陷入死循环问题求助

Let's get your code working properly to delete rows where column B is blank in Sheet1. Here's what went wrong and how to fix it:

What Caused the Problem? #

1. Incorrect Condition Check: Your code was deleting rows if either column A was empty OR column B was empty (IsEmpty(ws.Cells(iCntr, 1)) Or Trim(ws.Cells(iCntr, 2).Value) = ""), which wasn't your intended behavior. This could lead to deleting far more rows than expected, causing Excel to appear unresponsive (what you thought was a dead loop).
2. Redundant Activate Calls: Activating workbooks/worksheets isn't necessary for VBA to manipulate them, and it can introduce unexpected behavior.
3. Underutilized With Block: You wrapped your code in With ws but didn't use the dot notation to reference the worksheet's properties, which is inefficient.

Corrected Code #

Option Explicit
Sub DeleteBlankRows()
    Dim lRow As Long
    Dim iCntr As Long
    Dim ws As Worksheet
    Dim wkbk1 As Workbook
    
    ' Ensure the target workbook is open (add error handling if needed)
    On Error Resume Next
    Set wkbk1 = Workbooks("test.xlsm")
    On Error GoTo 0
    
    If wkbk1 Is Nothing Then
        MsgBox "Workbook 'test.xlsm' is not open!", vbExclamation
        Exit Sub
    End If
    
    ' Set reference to Sheet1
    Set ws = wkbk1.Worksheets("Sheet1")
    
    With ws
        ' Get last used row in column B
        lRow = .Range("B" & .Rows.Count).End(xlUp).Row
        
        ' Loop from last row UP to 1 to avoid skipping rows when deleting
        For iCntr = lRow To 1 Step -1
            ' Check ONLY if column B is empty (trimmed to handle spaces)
            If Trim(.Cells(iCntr, 2).Value) = "" Or IsEmpty(.Cells(iCntr, 2)) Then
                .Rows(iCntr).Delete
            End If
        Next iCntr
    End With
End Sub

Key Improvements Explained #

• Targeted Condition: Now we only check if column B is empty (including cells with just spaces, thanks to Trim).
• Error Handling: Added a check to ensure test.xlsm is open, so you get a clear message instead of a vague error.
• Efficient Object Reference: Used dot notation ( .Range, .Cells) inside the With ws block to directly reference the worksheet—no need for Activate.
• Safe Row Deletion: Looping from the last row up to the first ensures we don't skip rows when deleting (since deleting a row shifts rows below up, but moving upwards avoids this issue).

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