excel - 使用 VBA 删除 Excel 表格行

Question

我正在尝试删除 TEAMS 的行表当Value2列 D不匹配变量 department (字符串)。我能够检测到我想要删除的行，但删除方法会导致“delete method of range class failed”错误。

这是一个简化的代码片段。

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

Answer 1

您在 ListObject/table - 使用 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

非常类似于 this answer .