Excel VBA中如何删除以ListObject为数据源的ListBox多选行？

问题场景 #

UserForm中的ListBox以ListObject作为RowSource，需要实现选中多行后删除ListObject中对应的行并更新ListBox，但当前代码仅能删除选中行中最底部的一行，与选择顺序无关。原代码如下：

Private Sub Delete_Click()

'Option Explicit is enabled

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim myListBox As MSForms.listbox
    Dim i As Long
    
    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("Table1")
    Set myListBox = Me.listbox
    
    With myListBox
        For i = .ListCount - 1 To 0 Step -1
            If .Selected(i) Then
                tbl.ListRows(i + 1).Delete
            End If
        Next i
    End With
    
    myListBox.RowSource = ""
    myListBox.RowSource = tbl
End Sub

问题原因 #

在循环删除ListObject行的过程中，由于ListBox与ListObject通过RowSource绑定，删除操作会实时同步到ListBox，导致ListBox的ListCount和行索引发生变化，后续循环的选中项索引与ListObject的实际行索引不再匹配，最终只能删除最后一行选中项。

修正方案 #

先将所有选中的ListBox行索引（对应ListObject的行号）存储到集合中，再从后往前批量删除ListObject的行，最后更新ListBox的RowSource：

Private Sub Delete_Click()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim myListBox As MSForms.ListBox
    Dim i As Long
    Dim deleteRows As Collection
    
    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("Table1")
    Set myListBox = Me.ListBox
    Set deleteRows = New Collection
    
    ' 收集所有需要删除的ListObject行号（ListBox索引+1）
    With myListBox
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                deleteRows.Add i + 1
            End If
        Next i
    End With
    
    ' 从后往前删除，避免行索引偏移
    For i = deleteRows.Count To 1 Step -1
        tbl.ListRows(deleteRows(i)).Delete
    Next i
    
    ' 同步更新ListBox内容
    myListBox.RowSource = ""
    myListBox.RowSource = tbl.Name
End Sub

关键说明 #

• 先收集待删除行号：避免删除操作导致的索引实时变化影响后续判断
• 倒序删除ListObject行：从最后一行开始删除，前面的行索引不会因后续删除操作而偏移
• 最后更新RowSource：确保ListBox内容与ListObject完全同步

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