CODEWITHSUNDEEP
vbaexcellistobject
Maycl
Maycl

Reputation: 33

ListRow.Delete method doesn't work on filtered table

I am trying to figure out why the VBA ListRow.Delete method seems to work differently when my table is filtered and when it isn't.

I have a table with e-mail addresses, and have a column called AutoCheck with a formula that checks if the address fulfills my validity criteria. I also have a macro (tied to some buttons) that removes all the rows of the table that have the AutoCheck value at False:

Sub FixErrors()

    Dim tbl As ListObject
    Dim x As Long
    Set tbl = ActiveSheet.ListObjects("Kontakty")

    For x = tbl.ListRows.Count To 1 Step -1
        If tbl.ListRows(x).Range.Columns(tbl.ListColumns("AutoCheck").Index) = False Then
            tbl.ListRows(x).Delete
        End If
    Next x


End Sub

This works like a charm when I have the whole table displayed, but when I apply a filter and display only some entries (whether the offending rows are filtered out or not), the macro suddenly stops working! I have tried some debugging and remain completely baffled. I set a breakpoint at the line of tbl.ListRows(x).Delete, and it correctly identifies the offending row (a watch for tbl.ListRows(x).Range.Columns(tbl.ListColumns("Email").Index) displays the correct email I need to remove), but the tbl.ListRows(x).Delete just doesn't do anything, I step through it and nothing changes!

I'm sure I'm missing something here, I'd appreciate any help.

Upvotes: 3

Views: 3158

Answers (2)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

This behaviour in Tables seems to be by design. I'm not sure why it is so or what the consequences of allowing filtered table row deletion may be. If you right-click a filtered table cell then go to Delete, your options are Table Columns or Entire Sheet Row.

Running your code throws a run-time 1004 at the tbl.ListRows(x).Range.Delete line:

Can't move cells

However, given the right-click Delete options described above, it should be possible to delete the EntireRow:

Sub FixErrors()
Dim tbl As ListObject
Dim x As Long

    Set tbl = ActiveSheet.ListObjects("Kontakty")

    For x = tbl.ListRows.Count To 1 Step -1
        If tbl.ListRows(x).Range.Columns(tbl.ListColumns("AutoCheck").Index) = False Then
            tbl.ListRows(x).Range.EntireRow.Delete
        End If
    Next x

End Sub

Tested in Excel 365 and it works!

Upvotes: 3

MiS
MiS

Reputation: 79

See if this works:

Sub FixErrors()

   Dim tbl As ListObject
   Dim x As Long
   Set tbl = ActiveSheet.ListObjects("Kontakty")

   Application.DisplayAlerts = False

   For x = tbl.ListRows.Count To 1 Step -1
       If tbl.ListRows(x).Range.Columns(tbl.ListColumns("AutoCheck").Index) = False Then
          tbl.ListRows(x).Range.Delete
       End If
   Next x

   Application.DisplayAlerts = True

End Sub

Upvotes: 1

Related Questions