Excel VBA删除选中行按钮失效：匹配正确却误删表格上方行

使用Excel UserForm的cmbDelete按钮删除指定行时出现异常：调试显示Shop Order Number匹配正确，但会误删表格上方的第一行。表格表头在第3行，数据区域从第4行开始。以下是失效代码、可正常运行代码，以及两者的差异和问题原因分析。

失效代码 #

'Delete Button code 2 Not working
Private Sub cmbDelete_Click()
If MsgBox("Are you sure?", vbYesNo + vbCritical + vbDefaultButton2, "Delete this order") = vbYes Then

    Dim son As String
    son = Trim(txtShopOrdNum)
    Dim orderTable As ListObject
    Set orderTable = Worksheets("MASTER").ListObjects("MASTER")
    Dim matchRow
    matchRow = Application.Match(son, orderTable.ListColumns("SHOP ORDER NUMBER").DataBodyRange, 0)
    Rows(matchRow).Delete
    MsgBox ("Shop Order deleted.")
Else
    MsgBox "Deletion canceled."
'do nothing
End If

Dim ctrl As Variant
For Each ctrl In Controls
    If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""
Next ctrl

Call Main 'Progress Bar

lstMaster.List = Sheets("Master").Range("A4:DK" & Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row).Value
lblCount = lstMaster.ListCount
Debug.Print matchRow
End Sub

可正常运行代码 #

'Delete Button
Private Sub cmbDelete_Click()
    Dim orderTable As ListObject 'Best option for reading/writing to a worksheet with a table
    Set orderTable = Worksheets("Master").ListObjects("MASTER")
    Dim Shop_Order_Number As String
    Shop_Order_Number = Trim(txtShopOrdNum)
    Dim i As String
    Dim ctrl As Variant
    Dim answer As Integer
        answer = MsgBox("Are you sure?", vbYesNo + vbCritical + vbDefaultButton2, "Delete this order")
            If answer = vbYes Then
            MsgBox ("Shop Order deleted.")
            Else
        MsgBox "Deletion canceled."
        'do nothing
            End If
    With Worksheets("Master")
            i = WorksheetFunction.Match(Shop_Order_Number, .Range("E:E"), 0)
            If Err.Number = 0 Then
                Rows(i).EntireRow.Delete
            End If
    End With
    For Each ctrl In Controls  'The following code clears the textboxes'
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""
    Next ctrl
Call Main 'Progress Bar
lstMaster.List = Sheets("Master").Range("A4:DK" & Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row).Value
lblCount = lstMaster.ListCount
Debug.Print i
End Sub

核心差异及问题原因 #

1. Match返回值的含义完全不同 #

• 失效代码中，Application.Match作用于ListObject的DataBodyRange（即表格的数据区域，从第4行开始），返回的是数据区域内的相对行号（比如匹配到数据区域第1行，返回1）。但代码直接用Rows(matchRow).Delete，这会删除工作表的第1行，也就是误删了表格上方的行。
• 正常代码中，WorksheetFunction.Match作用于整个E列，返回的是工作表的绝对行号（比如匹配到第4行数据，返回4），所以能精准删除目标行。

2. 错误处理的缺失 #

• 失效代码没有处理Match可能返回的错误值（比如未找到匹配项时，matchRow会是错误值），此时执行Rows(matchRow).Delete会引发错误，甚至可能意外操作行。
• 正常代码通过If Err.Number = 0 Then判断匹配是否成功，避免了无匹配时的错误操作。

3. ListObject的使用错误 #

失效代码虽然定义了ListObject，但没有利用它的行操作方法。正确做法应该是用orderTable.ListRows(matchRow).Delete，这样直接操作表格内的行，不需要考虑工作表的绝对行号，逻辑更安全清晰。

4. 消息框逻辑顺序 #

失效代码是先删除行再弹出删除成功提示，正常代码是先弹提示再执行删除（虽然不影响功能，但逻辑顺序更合理）。

优化后的正确代码（基于ListObject） #

Private Sub cmbDelete_Click()
    If MsgBox("Are you sure?", vbYesNo + vbCritical + vbDefaultButton2, "Delete this order") = vbYes Then
        Dim son As String
        son = Trim(txtShopOrdNum)
        Dim orderTable As ListObject
        Set orderTable = Worksheets("MASTER").ListObjects("MASTER")
        Dim matchRow As Variant
        
        matchRow = Application.Match(son, orderTable.ListColumns("SHOP ORDER NUMBER").DataBodyRange, 0)
        
        ' 判断是否匹配到有效行
        If Not IsError(matchRow) Then
            ' 直接删除ListObject内的对应行，无需计算工作表绝对行号
            orderTable.ListRows(matchRow).Delete
            MsgBox "Shop Order deleted."
        Else
            MsgBox "未找到匹配的Shop Order Number。"
        End If
    Else
        MsgBox "Deletion canceled."
    End If

    ' 清空控件内容
    Dim ctrl As Variant
    For Each ctrl In Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""
    Next ctrl

    Call Main 'Progress Bar
    lstMaster.List = Sheets("Master").Range("A4:DK" & Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row).Value
    lblCount = lstMaster.ListCount
    Debug.Print matchRow
End Sub

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