CODEWITHSUNDEEP
excelvba
Kraal
Kraal

Reputation: 2877

Delete Excel Table row using VBA

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

Upvotes: 1

Views: 161

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

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.

Upvotes: 2

Related Questions