自动筛选错误

Question

我想为excel工作表创建一个自动筛选宏，它将筛选出Column E中不包含"ballroom*"的任何行，但也会留下Column E为空的所有行

我有基本的编程知识，教会了我到目前为止在VBA中所知道的东西

这就是我目前所拥有的

Sub row_deleter()

    Dim ws As Worksheet
    Dim rng As Range
    Dim lastrow As Long

    ''setting varibles
    Set ws = ActiveSheet

    lastrow = ws.Range("E" & ws.Rows.count).End(xlUp).Row

    Set rng = ws.Range("E1:E" & lastrow)

    ''actual filter function

    With rng
        .AutoFilter field:=5, Criteria1:=IsEmpty(rng), Operator:=xlAnd, Criteria2:="=*ballroom*"
        .SpecialCells(xlCellTypeVisible).EntireRow.delete
    End With

    ''turn off filters
    ws.AutoFilterMode = False


End Sub

When I try to run this code it gives me a 1004 error saying `AutoFilter` method of range class failed, and the debug points to the `AutoFilter` line. Have tried a few things thus far with syntax etc and nothing seems to be working.

Answer 1

首先，让我们确保您的表有一个AutoFilter。此外，您的标准不应该与任何范围相关，只应与正在过滤的内容相关。另外，我认为你的标准应该是xlOr -一个单元格不能是空的，里面不能有舞厅。试试这个：

Sub row_deleter()

    Dim ws As Worksheet
    Dim rng As Range
    Dim lastrow As Long

    ''setting varibles
    Set ws = ActiveSheet

    lastrow = ws.Range("E" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("E1:E" & lastrow)

    ''turn on autofilter if it's off
    If ws.AutoFilterMode = False Then
        ws.UsedRange.AutoFilter
    End If

    ''actual filter function
    With rng
        .AutoFilter Field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:="=*ballroom*"
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ''turn off filters
    ws.AutoFilterMode = False

End Sub

Answer 2

field:=是一个偏移量，您只有一列作为您的范围。你想让它成为field:=1

你也在使用你想要的xland。不能同时有空单元格和有交际舞的单元格。

.AutoFilter Field:=1, Criteria1:=IsEmpty(rng), Operator:=xlOr, Criteria2:="=*ballroom*"

Answer 3

我认为你已经得到了答案，并且因为你的评论(你想删除每一行与你的标准不匹配的行)，我调整了你的代码，使它更容易阅读和执行你真正想要的东西：

Option Explicit
Sub row_deleter()

    Dim lastrow As Long

    ''setting varibles
    'you can use a With ActiveSheet and avoid the use of ws Thought I wouldn't recommend using ActiveSheet unless you attach
    'this macro to a button on the sheet itself.
    With ActiveSheet
        lastrow = .Range("E" & .Rows.Count).End(xlUp).Row
        ''actual filter function
        .UsedRange.AutoFilter Field:=5, Criteria1:="<>", Operator:=xlOr, Criteria2:="<>*ballroom*"
        .Range("A2:A" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        ''turn off filters
        .AutoFilterMode = False
    End With

End Sub