Excel VBA autofilter all but three

问题:

In the continuing saga of my data analysis (First Question), I want to delete all the rows whose departments (Field 7) are not 101, 102 or 103 (the names have been changed to protect the innocent). There are about a hundred departments in the data, so using Criteria1:=Array("104", "105", "106", etc is impractical.

I would like to do something like this:

myrange.AutoFilter Field:=7, Criteria1:="<>101", Operator:=xlOr, _
    Criteria2:="<>102", Operator:=xlOr, Criteria3:="<>103"

but Excel doesn't recognize more than 2 Criteria. I could add a helper column, and have the macro run through each line (if 101, 102, or 103, then value=Yes), filter out the yeses, and delete all that remain, but I'm saving that as a last resort.

Is there a way to Autofilter Criteria1 to be Not Equal To an array? Something like:

myrange.AutoFilter Field:=7, Criteria1:="<>" & Array("101", "102", "103")

回答1:

Remember the goal is to delete the non-matching rows; AutoFilter is only one tool to help achieve the goal. If AutoFilter does not meet your needs, pick another method. Consider:

Sub AllBut()
    Dim rTable As Range, r As Range
    Dim rDelete As Range
    Set rTable = Selection
    Set rDelete = Nothing
    For Each r In rTable.Columns(7).Cells
        v = r.Value
        If v <> "101" And v <> "102" And v <> "103" Then
            If rDelete Is Nothing Then
                Set rDelete = r
            Else
                Set rDelete = Union(r, rDelete)
            End If
        End If
    Next

    If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub

Here we select the block of data to be processed (not including the header row). The macro sweeps down column #7 of that block and deletes any row that does not match the criteria.

All that will remain are the 101's, the 102's, and the 103's.

回答2:

Since this was about the AutoFilter method, I'll offer up this method involving using a Scripting.Dictionary object to mimic the procedure that would be used if this was manually performed on the worksheet.

On the worksheet, the user would apply an AutoFilter then use column G's drop down to 'turn off' the 101, 102 and 103 values. What remained would be be deleted. In VBA, we can grab all of column G and populate a dictionary object with the values that are not 101, 102 or 103 and use that as the criteria for the filter operation.

Sub filterNotThree()
    Dim d As Long, dDELs As Object, vVALs As Variant

    Set dDELs = CreateObject("Scripting.Dictionary")

    With Worksheets("Sheet6")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            'grab all of column G (minus the header) into a variant array
            vVALs = .Resize(.Rows.Count - 1, 1).Offset(1, 6).Value2

            'populate the dictionary object with the values that are NOT 101, 102, or 103
            For d = LBound(vVALs, 1) To UBound(vVALs, 1)
                Select Case vVALs(d, 1)
                    Case 101, 102, 103
                        'do not add
                    Case Else
                        'not a match, add it to the delete list
                        'the AutoFilter criteria needs to be text
                        ' so we set the Keys as text and the Items as numbers
                        dDELs.Item(CStr(vVALs(d, 1))) = vVALs(d, 1)
                End Select
            Next d

            'check to make sure there is something to filter on
            If CBool(dDELs.Count) Then
                'filter on the dictionary keys
                .AutoFilter field:=7, Criteria1:=dDELs.keys, Operator:=xlFilterValues

                'delete the visible rows (there has to be some)
                .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).EntireRow.Delete
            End If

        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With

    dDELs.RemoveAll: Set dDELs = Nothing
End Sub

        
                ^{Data before filterNotThree sub procedure}

        
                ^{Data after filterNotThree sub procedure}

回答3:

I was doing something similar but for two fields and this syntax worked for me:

myrange.AutoFilter Field:=7, Criteria1:="<>101", Operator:=xlAnd, Criteria2:="<>102", Operator:=xlAnd

Hope it helps.