excel - 删除选定的行

Question

目的:在表格中，允许用户选择行(SELECTION所在的行)，按下快捷键并删除那些行。无论它们是否被过滤并且选择是否在非连续范围内。

我从另一个站点获得并修改了以下代码:

问题各不相同，从运行时错误 1004:无法移动过滤范围或表格中的单元格到类的删除方法失败(或者其他，它发生的频率低于第一个一)

Sub DeleteTableRows()
    'PURPOSE: Delete table row based on user's selection
    'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

    Dim rng As Range
    Dim DeleteRng As Range
    Dim cell As Range
    Dim TempRng As Range
    Dim Answer As Variant
    Dim area As Range
    Dim ReProtect As Boolean
    Dim copyRange As Range
    Dim pasteRange As Range
    Dim wb As Workbook
    Dim a As Long

    'Set Range Variable
      On Error GoTo InvalidSelection
        Set rng = Selection
      On Error GoTo 0


    'Unprotect Worksheet
      With ThisWorkbook.ActiveSheet
        If .ProtectContents Or ProtectDrawingObjects Or ProtectScenarios Then
          On Error GoTo InvalidPassword
          .Unprotect Password
          ReProtect = True
          On Error GoTo 0
        End If
      End With

      Set wb = ThisWorkbook

    'Loop Through each Area in Selection
      For Each area In rng.Areas
        For Each cell In area.Cells.Columns(1)
          'Is selected Cell within a table?
            InsideTable = True

          'Gather rows to delete
            If InsideTable Then
              On Error GoTo InvalidActiveCell
              Set TempRng = Intersect(cell.EntireRow, ActiveCell.ListObject.DataBodyRange)
              On Error GoTo 0

              If DeleteRng Is Nothing Then
                Set DeleteRng = TempRng
              Else
                Set DeleteRng = Union(TempRng, DeleteRng)
              End If

            End If

        Next cell
      Next area


    'Error Handling
      If DeleteRng Is Nothing Then GoTo InvalidSelection
      If DeleteRng.Address = ActiveCell.ListObject.DataBodyRange.Address Then GoTo DeleteAllRows
      If ActiveCell.ListObject.DataBodyRange.Rows.Count = 1 Then GoTo DeleteOnlyRow

    'Ask User To confirm delete (since this cannot be undone)
        DeleteRng.Select

        If DeleteRng.Rows.Count = 1 And DeleteRng.Areas.Count = 1 Then
          Answer = MsgBox("Are you sure you want to delete the currently selected table row? " & _
           " This cannot be undone...", vbYesNo, "Delete Row?")
        Else
          Answer = MsgBox("Are you sure you want to delete the currently selected table rows? " & _
           " This cannot be undone...", vbYesNo, "Delete Rows?")
        End If

    'Delete row (if wanted)
      If Answer = vbYes Then

        'this part is giving me troubles
        For a = DeleteRng.Areas.Count To 1 Step -1
            Debug.Print DeleteRng.Areas.Count
            DeleteRng.Areas(a).EntireRow.Delete
        Next a

      End If

    'Protect Worksheet
      If ReProtect = True Then wb.Worksheets("Open Orders").Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=False _
    , AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True  

End Sub

Answer 1

我认为您在这里遇到了一些问题，但肯定有一个，这似乎有悖常理。

当以编程方式删除多个不连续的行/列/单元格/区域时，最好反向操作。

---

当您删除一行时，Excel 会将其下方的行上移。因此，后续的行号很容易混淆，导致错误，或者更糟糕的是，无意中丢失数据。

Example

Imagine you want to delete rows 1, 4, 5 and 7. If you delete them one at a time, starting at the top, then you'll delete row 1, which makes the other rows numbers to delete 3, 4 and 6. Delete 3 and now you need to delete 3 and 5.

To remove rows 1, 4, 5 and 7 one at a time, starting at the top, you'll actually need to delete rows 1, 3, 3, and 4 (yes, you'd be deleting row 3 twice).

---

有几种解决方法:

1. 一次删除所有行。您可以使用 the Union method 加入每个选定的行然后一次删除整个范围。

或者，我的偏好:

1. 向后 遍历行，从数据的底部开始，逐步向上。由于 For..Each 循环无法反向执行，因此您需要切换到 For..Next。

   您可以使用 the Range.End property 找到最后填充的行(在我的示例中使用列 A) , 然后使用 the Intersect method将每一行与 user's .Selection 进行比较行和/或单元格。如果他们两个相交，那么你可以.Delete the row .

---

示例:

Sub DeleteSelectedRows()
    Dim rw As Long, lastRow As Long, del As Long
    With Workbooks("book1").Sheets("Sheet1")
        lastRow = .Cells(ws.Rows.Count, 1).End(xlUp).Row 'find last row of Column #1 (A)
        For rw = lastRow To 1 Step -1 'loop through rows backwards
            If Not Intersect(Selection, Rows(rw).Cells) Is Nothing Then
                'This row is within the selected worksheets range(s)
                Rows(rw).Delete 'delete row
                del = del + 1 'count deletion (only for troubleshooting)
            End If
        Next rw
    End With
    MsgBox del & " rows were deleted."
End Sub

上述过程将需要进行一些小的改动以调整工作表上数据的位置，但对我来说是完美的测试。

请注意，我上面的帖子中有几个链接...在使用您不熟悉的命令之前，请务必阅读官方文档。这也有助于术语，因为有很多术语需要习惯! ...例如您如何滥用术语 Selection...除非您使用 Select 方法，否则 VBA 不会选择行。常见的错误。 :-) 祝你好运!