CODEWITHSUNDEEP
vbaexcel-2007delete-row
David Gard
David Gard

Reputation: 12047

Delete a row in a filtered table

I have a table in Excel which is filtered to only show certain data, and I need to then manually assess this data to row by row to see whether or not it should be kept or deleted.

I have macro below for deleting a row (activated by pressing Ctrl+d), which works fine on a non-filtered table, but when a filter is applied I get this error -

Cannot shift cells in a filtered range or table

Can anyone please help me amend the macro so that it works in with a filtered table?

Sub deleteTableRow()

    Dim rng As Range

    On Error Resume Next
    With Selection.Cells(1)
        Set rng = Intersect(.EntireRow, ActiveCell.ListObject.DataBodyRange)
        On Error GoTo 0
        If rng Is Nothing Then
            MsgBox "Please select a valid table cell.", vbCritical
        Else
            rng.Delete xlShiftUp
        End If
    End With

End Sub

Upvotes: 1

Views: 5869

Answers (3)

Michal aka Miki
Michal aka Miki

Reputation: 1

Just for future - that's normal error, when you hide some columns in filtered view. Deleting entire rows works, but it's not always recommended (i.e. when you have more than one table in your sheet)

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

The error message is pretty unambiguous: You can't perform this operation in a table that's filtered.

row.EntireRow.Delete Should work :)

Upvotes: 0

David Gard
David Gard

Reputation: 12047

Ok, I figured it out.

I don't know why, but removing the Shift parameter from the Delete method worked. It did initially display a prompt to ask me to confirm the deletion, but setting DisplayAlerts to false before the delete works (and of course back to true afterwards).

Sub deleteTableRow()

    Dim row As Range    ' The table row to delete

    On Error Resume Next
    With Selection.Cells(1)
        Set row = Intersect(.EntireRow, ActiveCell.ListObject.DataBodyRange)
        On Error GoTo 0
        If row Is Nothing Then
            MsgBox "Please select a valid table cell.", vbCritical
        Else
            Application.DisplayAlerts = False
            row.Delete
            Application.DisplayAlerts = True
        End If
    End With

End Sub

Upvotes: 1

Related Questions