Excel表格一次性删除不相邻多行报错，求非循环/取消列表解决方案

方法一：通过收集表格行对象批量删除 #

先定位选中区域所属的结构化表格，遍历选中单元格收集对应的ListRow对象（自动去重），最后倒序删除这些行——倒序操作可避免删除行后索引偏移导致的漏删问题。

代码示例：

Sub DeleteTableSelectedRows()
    Dim tbl As ListObject
    Dim selectedRows As Collection
    Dim cell As Range
    Dim listRow As ListRow
    
    ' 获取选中区域所属的表格
    On Error Resume Next
    Set tbl = Selection.ListObject
    On Error GoTo 0
    
    If tbl Is Nothing Then Exit Sub ' 选中区域不在表格内则直接退出
    
    Set selectedRows = New Collection
    
    ' 遍历选中单元格，收集对应的表格行（去重）
    For Each cell In Selection
        Set listRow = tbl.ListRows(cell.Row - tbl.HeaderRowRange.Row)
        On Error Resume Next
        selectedRows.Add listRow, Key:=CStr(listRow.Index)
        On Error GoTo 0
    Next cell
    
    ' 倒序删除行，防止索引混乱
    Dim i As Integer
    For i = selectedRows.Count To 1 Step -1
        selectedRows(i).Delete
    Next i
End Sub

方法二：利用字典获取唯一行索引删除 #

通过Intersect精准定位选中区域在表格数据区内的部分，用字典提取唯一的表格行索引，再倒序删除对应行，效率更高。

代码示例：

Sub DeleteTableRowsFromSelection()
    Dim tbl As ListObject
    Dim rng As Range
    Dim rowIndices As Variant
    Dim i As Long
    
    Set tbl = ActiveCell.ListObject
    Set rng = Intersect(Selection, tbl.DataBodyRange)
    
    If rng Is Nothing Then Exit Sub ' 选中区域无表格数据行则退出
    
    rowIndices = GetUniqueRowIndices(rng)
    
    ' 倒序删除表格行
    For i = UBound(rowIndices) To LBound(rowIndices) Step -1
        tbl.ListRows(rowIndices(i)).Delete
    Next i
End Sub

' 辅助函数：提取选中区域对应的唯一表格行索引
Function GetUniqueRowIndices(rng As Range) As Variant
    Dim dict As Object
    Dim cell As Range
    Dim tbl As ListObject
    
    Set tbl = rng.ListObject
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cell In rng
        dict(cell.Row - tbl.HeaderRowRange.Row) = True
    Next cell
    
    GetUniqueRowIndices = dict.Keys
End Function

报错原因说明 #

直接使用Selection.EntireRow.Delete触发1004错误，是因为Excel结构化表格（ListObject）的行与工作表行绑定了结构化规则，直接删除整工作表行会和表格的扩展逻辑、关联特性冲突，导致操作失败。

内容的提问来源于stack exchange，提问作者M.Zayed