Question

我正试图从42到48年删除一栏的数值。

有一种错误的信息

2. 界定的错误

Sub FilterDeleteVisible()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("DO") 
    Call FilterAndDelete(ws, "E", "42*", "48*")    
End Sub

Sub FilterAndDelete(ws As Worksheet, col As String, ParamArray criteria() As Variant)
    Dim rng As Range
    Dim MyArray As Variant
    MyArray = criteria
    Set rng = ws.Range(col & ":" & col)
    rng.AutoFilter Field:=1, Criteria1:=MyArray, Operator:=xlFilterValues
    rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ws.AutoFilterMode = False
End Sub

Answer 1

<><>><>>说明:<>>>> (Thanks for @MGonet s comments)

As wildcards are used, it only filters text and number as text. That is, it doesn work for numbers in the column.
Operator:=xlFilterValues doesn t support wildcards criterias. It is converted to xlOr if there are only two items (with wildcards) in MyArray.

rng.AutoFilter Field:=1, Criteria1:=MyArray(0), Criteria1:=MyArray(1), Operator:=xlOr

rng is a range object of Column. There isn t more space (cells) on worksheet for rng.Offset(1, 0).
Use End(xlUp) to get the last used cell on the column
It s more reliable to resize range before using offset. rng.Resize(rng.Rows.Count - 1).Offset(1, 0)
OERN is used to ignore error when there isn t any visible rows after filtering.

Sub FilterAndDelete(ws As Worksheet, col As String, ParamArray criteria() As Variant)
    Dim rng As Range, lastRow As Long, c As Range
    Dim MyArray As Variant
    MyArray = criteria
    With ws
        If .AutoFilterMode Then .AutoFilter.ShowAllData
        Set rng = .Range(.Cells(1, col), .Cells(.Rows.Count, col).End(xlUp))
    End With
    If UBound(MyArray) = 1 Then
        rng.AutoFilter Field:=1, Criteria1:=MyArray(0), Criteria1:=MyArray(1), Operator:=xlOr
    Else
          Doesn t support wildcards if more than 2 items in MyArray
        rng.AutoFilter Field:=1, Criteria1:=MyArray, Operator:=xlFilterValues
    End If
    On Error Resume Next  OERN for blank
    Set c = rng.Resize(rng.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Not c Is Nothing Then
        c.EntireRow.Delete
    End If
    ws.AutoFilterMode = False
End Sub

Microsoft documentation:

结束语:

Answer 2

Filter By Numbers With Wild Characters Using the Like Operator

If your column contains numbers, AutoFilter won t work. Here s a workaround that ll be roughly as efficient as Range.Union will allow it.
It will also work for strings but I guess using AutoFilter should be faster.
It additionally allows you to have as many criteria as necessary.

<>Usage>

Sub FilterDeleteVisible()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("DO")
    FilterWcNumbers ws, "E", "42*", "48*"
End Sub

www.un.org/spanish/ecosoc 方法

Sub FilterWcNumbers(ByVal ws As Worksheet, ByVal Col As String, ParamArray Criteria() As Variant)
    
    If ws.FilterMode Then ws.ShowAllData
    
    Dim rg As Range: Set rg = Intersect(ws.UsedRange, ws.Columns(Col))
    If rg Is Nothing Then Exit Sub   no column
    If rg.Columns.Count > 1 Then Exit Sub   multiple columns
    
    Dim rCount As Long: rCount = rg.Rows.Count - 1
    If rCount = 0 Then Exit Sub   no data in column
    
    Set rg = rg.Resize(rCount).Offset(1)
    
    Dim Data() As Variant
    
    If rCount = 1 Then
        ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
    Else
        Data = rg.Value
    End If
    
    Dim cUpper As Long: cUpper = UBound(Criteria)
    
    Dim urg As Range, r As Long, c As Long
        
    For r = 1 To rCount
        For c = 0 To cUpper
            If CStr(Data(r, 1)) Like Criteria(c) Then
                If urg Is Nothing Then
                    Set urg = rg.Cells(r)
                Else
                    Set urg = Union(urg, rg.Cells(r))
                End If
                Exit For
            End If
        Next c
    Next r
    
    If urg Is Nothing Then Exit Sub   no matches found
    
    urg.EntireRow.Delete xlShiftUp

End Sub

Filter By Numbers With Wild Characters Using the Like Operator

友情链接