用我不想要的值过滤范围 [英] Filter a Range with values which I don't want

问题描述

ThisWorkbook.Sheets(1).Range("A1:AR1").AutoFilter Field:=27, _
Criteria1:=Array("<>DRCA", "<>DREX", "<>DRFU", "<>DRIN", _
"<>DRIR", "<>DRND", "<>DRPN", "<>DRPR", "<>DRRE", "<>DRUN", _
"<>REXC", "<>EXCD", "<>RFUR", "<>RINV", "<>RIRC", "<>RNDR", _
"<>RPNA", "<>RPRO", "<>RRET", "<>RUND", "<>RUNF", "<>EXC", "<>C"), _
Operator:=xlFilterValues

这没有返回我想要的正确过滤器数据.我想要的是，如果数组找不到任何<> value，请跳过它并检查下一个.

This is not returning correct filter data that I want. What I want is that if array doesn't find any <> value then skip it and check next.

我该怎么办?

推荐答案

Excel不允许您在Autofilter中使用所需的数组.但是还有其他选择吗?是的！

Excel doesn't allow you to use the array like you want it in the Autofilter. But is there an alternative? Yes!

逻辑如果我要求您在0和10之间选择数字，包括0和10，但不能选择0，5，8和10.因此，您可以说I want 1,2,3,4,6,7,9.而不是过滤掉数字并说我不想要0，5，8和10.

Logic If I ask you choose numbers between 0 and 10 including 0 and 10 but you cannot choose 0, 5, 8 and 10. So instead of filtering the numbers out and saying I don't want 0, 5, 8 and 10, you can say I want 1,2,3,4,6,7,9.

类似地，在您的情况下，我们不会根据不想要的值进行过滤.我们将根据要的值进行过滤.

Similarly in your case we will not filter on the values we DON'T want. We will filter on values we WANT.

那么如何，我们会找到该列表并将其存储在数组中吗?

So HOW do we find that list and store it in an array?

1. 在相关列中找到最后一行.
2. 将该列中的所有记录存储在一个唯一的集合中
3. 检查该集合中的哪些项目不在排除"列表中，并创建一个数组.
4. 在所需的值(数组)上过滤范围！这样，我们将不必筛选不需要的值.

代码(在Excel 2013中测试，具有5万条记录)

我已经注释了代码，但是如果您仍有任何疑问，请随时提出:)

I have commented the code but if you still have any questions then feel free to ask :)

Dim OmitArray As Variant
Const deLim As String = "|"

Sub Sample()
    Dim Ws As Worksheet
    Dim lRow As Long, i As Long, n As Long, lCol As Long
    Dim Col As New Collection, itm
    Dim includeArray As Variant
    Dim rng As Range
    Dim tmpString As String

    '~~> This array has all the values that you want to ignore
    OmitArray = Array("DRCA", "DREX", "DRFU", "DRIN", "DRIR", "DRND", _
                "DRPN", "DRPR", "DRRE", "DRUN", "REXC", "EXCD", "RFUR", _
                "RINV", "RIRC", "RNDR", "RPNA", "RPRO", "RRET", "RUND", _
                "RUNF", "EXC", "C")

    '~~> This is the column where you want to filter out
    lCol = 27

    '~~> Change this to the relevant worksheet
    Set Ws = ThisWorkbook.Sheets("Sheet1")

    With Ws
        '~~> Find lastrow
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> This is the range where the filter will be applied
        Set rng = .Range("A1:AR" & lRow)

        '~~> All all the values from col 27 to a unique collection
        For i = 2 To lRow
            On Error Resume Next
            Col.Add .Cells(i, lCol).Value, CStr(.Cells(i, 27).Value)
            On Error GoTo 0
        Next i

        '~~> Now loop though the collection and store the values in a string
        '~~> delimited with a delimiter which arenot present in the "OmitArray"
        For Each itm In Col
            If Not IsInArray(itm, OmitArray) Then
                If tmpString = "" Then
                    tmpString = itm
                Else
                    tmpString = tmpString & deLim & itm
                End If
            End If
        Next itm

        If tmpString <> "" Then
            '~~> Split the values based on the delimiter to create array
            includeArray = Split(tmpString, deLim)

            '~~> Remove any filters
            .AutoFilterMode = False

            '~~> Filter on the rest of the values
            With rng
              .AutoFilter Field:=lCol, Criteria1:=includeArray, Operator:=xlFilterValues
            End With
        End If
    End With
End Sub

'~~> Function to check if there is an item in the array
Function IsInArray(stringToBeFound As Variant, arr As Variant) As Boolean
    Dim bDimen As Byte, i As Long

    On Error Resume Next
    If IsError(UBound(arr, 2)) Then bDimen = 1 Else bDimen = 2
    On Error GoTo 0

    Select Case bDimen
    Case 1
        On Error Resume Next
        IsInArray = Application.Match(stringToBeFound, arr, 0)
        On Error GoTo 0
    Case 2
        For i = 1 To UBound(arr, 2)
            On Error Resume Next
            IsInArray = Application.Match(stringToBeFound, Application.Index(arr, , i), 0)
            On Error GoTo 0
            If IsInArray = True Then Exit For
        Next
    End Select
End Function

这篇关于用我不想要的值过滤范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！