Delete Excel Table row using VBA

Question

I'm trying to delete the lines of a TEAMS Table when the Value2 of column D does not match a variable department (String). I'm able to detect the rows I want to delete, but the delete method causes a "delete method of range class failed" error.

Here is a simplified code snippet.

Dim i As Integer
For i = 1 To wkbook.Worksheets("T").Range("TEAMS").Rows.Count
  With wkbook.Worksheets("T").Range("TEAMS[D]")(i)
    If .Value2 <> department Then
      .EntireRow.Delete
    End If
  End With
Next i

Possiblyy a standard row delete issue where you should loop from the bottom to the top. Try: For i = wkbook.Worksheets("T").Range("TEAMS").Rows.Count To 1 Step -1 — Tragamor
– Tragamor, Commented Sep 10, 2019 at 16:34

Mathieu Guindon · Accepted Answer · 2019-09-10 17:10:37Z

2

You're in a ListObject/table - use the ListObject API.

Dim table As ListObject
Set table = wkbook.Worksheets.ListObjects("TEAMS")

Dim colIndex As Long
Set colIndex = table.ListColumns("D").Index

Dim toDelete As Range

Dim currentRow As ListRow
For Each currentRow In table.ListRows
    If currentRow.Range.Cells(ColumnIndex:=colIndex).Value <> department Then
        If toDelete Is Nothing Then
            Set toDelete = currentRow.Range
        Else
            Set toDelete = Application.Union(toDelete, currentRow.Range)
    End If
Next

If Not toDelete Is Nothing Then toDelete.Delete

Pretty similar to this answer.

edited Sep 10, 2019 at 17:10

answered Sep 10, 2019 at 16:54

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

1 Comment

user6585761 Over a year ago

I'm assuming that it should be Table.ListRows, no?

Collectives™ on Stack Overflow

Delete Excel Table row using VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related