Delete selected rows

Question

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:

The problem varies, from runtime error 1004: can't move cells in a filtered range or table to delete method of class failed (or something, it happens less often than the first one)

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

ashleedawg · Accepted Answer

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.

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.

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).

There are a couple ways around this:

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.

Or, my preference:

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.

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.

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!

Delete selected rows

Answers (2)

Example

Related Questions