Delete table row from filtered ListObject

Question

I am trying to delete an entire row from a Table (ListObject), but I have issues adressing the Row. Following code works prefect when the table is not filtered:

Sub loeschen()

If errorhandling Then On Error GoTo fehlerbehandlung

    ActiveCell.ListObject.ListRows(ActiveCell.Row - ActiveCell.ListObject.HeaderRowRange.Row).Delete

Exit Sub
fehlerbehandlung:
fehlermeldung

End Sub

Filtering is done by user in Excel, not VBA. Is there a direct way to delete a row or do I have to unfilter it? And if that's the case, how do I filter it again the same way (the user shouldn't notice anything, I'll apply Application.ScreenUpdatingif necessary.

Rafa Barragan · Accepted Answer · 2018-09-05 14:55:58Z

1

If it is already filtered, you can try this:

ActiveSheet.ListObjects([Table_Name]).DataBodyRange.EntireRow.Delete

answered Sep 5, 2018 at 14:55

Rafa Barragan

6209 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlio · Accepted Answer · 2016-06-06 06:43:52Z

0

I figured it out.

You have to call the row this way

ActiveCell.ListObject.Range.EntireRow(ActiveCell.Row - ActiveCell.ListObject.HeaderRowRange.Row + 1).Delete

Even if filtered, the right row will be deleted. (Same thing for .Select)

answered Jun 6, 2016 at 6:43

Charlio

3964 silver badges14 bronze badges

Collectives™ on Stack Overflow

Delete table row from filtered ListObject

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related