删除选定的行 [英] Delete selected rows

问题描述

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

Objective: in a table, allow users to select rows (the rows where the SELECTION is), press a short cut and delete those rows. No matter if they are filtered and the selection is in non contiguous ranges or not.

我有下面的代码是我从另一个站点获得并修改的:

I have the code below I got from another site and modified it:

问题有所不同，从运行时错误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

推荐答案

我认为您在这里遇到了几个问题，但是可以肯定的是，这似乎是违反直觉的.

I think you've got a couple issues going on here, but one for sure, which might seem counter-intuitive.

以编程方式删除多个非连续的行/列/单元格/区域时，最好相反地进行.

When programmatically deleting multiple non-consecutive rows/columns/cells/areas, it's best to do so in reverse.

当您删除一行时，Excel会将其下方的行向上移动 .因此，随后的行号很容易混淆，从而导致错误，或者更糟糕的是，意外丢失了数据.

When you delete a row, Excel shifts the rows beneath it up. Therefore, the subsequent row numbers become easily confused, causing errors or, worse yet, unintentionally lost data.

示例

假设您要删除行1, 4, 5 and 7.如果您一次删除它们(从顶部开始)，则将删除行1，这将使其他行编号删除3, 4 and 6.删除3，现在您需要删除3 and 5.

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.

要一次从顶部开始一次删除行1, 4, 5 and 7，您实际上需要删除行1, 3, 3, and 4(是的，您将删除行3两次).

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. 一次删除所有行.您可以使用 Union方法 ，然后一次删除整个范围.

1. Delete all the rows at once. You could join each of the selected rows with the Union method and then delete the entire range at one.

或者，我的偏好:

1. 从数据底部开​​始依次向上遍历向后行.由于不能使For..Each循环反向，因此您需要切换到For..Next.

1. Iterate through the rows backwards, starting at the bottom of your data and working your way up. Since For..Each loop can't be made to go in reverse, you'll need to switch to a For..Next.

您可以使用 Range.End属性 ，然后使用 .Delete该行 .

You can find the last populated row (using column A in my example) with the Range.End property, and then use the Intersect method to compare each row to the user's .Selection of rows and/or cells. If they two intersect, then you can .Delete the row.

---

示例:

---

Example :

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

上面的过程将需要进行一些小的更改，以适应工作表上数据的位置，但是对我来说是完美的测试.

The above procedure will need some minor changes to adjust for the location of data on your worksheet, but tested perfectly for me.

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

Note that there are several links in my post above... always read the official documentation before using commands with which you are unfamiliar. This will also help with terminology, as there's a lot of it to get used to! ...such as how you were misusing the term Selection... VBA isn't selecting rows unless you're using the Select method. Common mistake. :-) Good luck!

这篇关于删除选定的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！