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