Filter函数应用示例

 

Excel帮助文件中没有Filter函数的应用示例，自已做了一个。

如图，要求分别列出A列从A10单元格开始的区域中包含及不包含字母“F”的所有值。

代码如下：

 Sub Test()
    Application.ScreenUpdating = False
    On Error Resume Next '否则，如果筛选结果为空，行列转置时会出错
    Dim iRowA As Integer, Arr(), Temp1() As String, Temp0() As String, cMatch As String, lRow1 As Long, lRow0 As Long
    
    iRowA = Range("a65536").End(xlUp).Row
    Arr = WorksheetFunction.Transpose(Range("a10:a" & iRowA))
    cMatch = "F"
    Temp1 = Filter(Arr, cMatch, True)    '默认为True
    Temp0 = Filter(Arr, cMatch, False)
    lRow1 = UBound(Temp1) + 1
    lRow0 = UBound(Temp0) + 1
    
    [B9] = "A10:A" & iRowA & "区域中包含" & cMatch & "的有"
    [E9] = "A10:A" & iRowA & "区域中不包含" & cMatch & "的有"
    
    With WorksheetFunction
        'Range("b10:b" & (10 + lRow1 - 1)) = .Transpose(Temp1)  '或
        Range("b10").Resize(lRow1, 1) = .Transpose(Temp1)
        Range("e10").Resize(lRow0, 1) = .Transpose(Temp0)
    End With
    
    Application.ScreenUpdating = True
End Sub