我想使用vba删除excel中与特定列值匹配的所有行,但遇到错误

Sub Delete_Rows()

Dim lo As ListObject 
Dim ActiveSheet As String
Dim wkbSource As Workbook

Application.ScreenUpdating = False

Set wkbSource = Workbooks.Open("C:\Users\nani\Desktop\11.0\deleteRows\abc.xlsx")

Set ActiveSheet = wkbSource.Sheets("LIST")

'perform delete

Set lo = ActiveSheet.ListOjects(1)

lo.Range.AutoFilter Field:=12, Criteria:="" Or "claimed"

Application.DisplayAlerts = False

lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete

Application.DisplayAlerts = True

wkbSource.Close SaveChanges:=True

End Sub
评论
  • yeum
    yeum 回复

    一些修复:

    Sub Delete_Rows()
    
    Dim lo As ListObject 
    Dim ws As Worksheet '<<<<<<
    Dim wkbSource As Workbook
    
    Application.ScreenUpdating = False
    
    Set wkbSource = Workbooks.Open("C:\Users\nani\Desktop\11.0\deleteRows\abc.xlsx")
    
    Set ws = wkbSource.Sheets("LIST")
    
    Set lo = ws.ListObjects(1)  '<< typo
    
    'fixed...
    lo.Range.AutoFilter Field:=12, Criteria1 := "=", _
                               Operator := xlOr, _
                               Criteria2 := "claimed"
    
    Application.DisplayAlerts = False
    
    lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    
    Application.DisplayAlerts = True
    
    wkbSource.Close SaveChanges:=True
    
    End Sub