我想使用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
点赞
评论