0

Please  I have this function, which is part of code, it replaces my old way of filtering, I want to use it as a standalone code for filtering in excel in place of my old filtering code which can be found here

Excel VBA Loop Copy/paste, autofilter, return value and loop

I want to be able to use it in any active sheet just like I used the old one. By just pressing a shortcut like ctrl + w and it works on that sheet without returning any value whatsoever, or confirming sheet name and all that.

Please if there's a way this can be done, I'd greatly appreciate.

Function FilterX(ws As Worksheet) As Long
    Dim rng As Range, dict, c    
    Dim lastrow As Long, n As Long    
    Set dict = CreateObject("Scripting.Dictionary")    

    ' configure filter column, tolerance    
    With dict    '   
        .Add "L"
        .Add "M", 0 ' +/- 0
    '   .Add "N",
        .Add "O", 1 ' +/- 1
    '   .Add "P",
    '   .Add "Q",
        .Add "R", 2
    '   .Add "S",
    '   .Add "T",
        .Add "U", 1
    '   .Add "V",
        .Add "W", 1
    '   .Add "X",
    '   .Add "Y",
        .Add "Z", 2
    End With

    With ws
        ' remove filter
        If .FilterMode = True Then .ShowAllData
       
        ' apply fliter
        lastrow = .UsedRange.Row + .UsedRange.Rows.Count - 1
        If lastrow < 3 Then
             FilterX = 0
             Exit Function
        End If
        
        Set rng = .Range("A1:AZ" & lastrow)
        'Debug.Print ws.Name, rng.Address
        
        ' apply filter to columns M, O, R, U, W, Z
        For Each c In dict.keys
            n = Cells(1, c).Column ' column number
            ' dict(c) is tolerance +/- on rows 2 value
            rng.AutoFilter Field:=n, Criteria1:=">=" & (.Cells(2, n) - dict(c)), _
                    Operator:=xlAnd, Criteria2:="<=" & (.Cells(2, c) + dict(c))
        Next
        
        ' return count
        On Error Resume Next 'skips error code when no cells are found
        FilterX = .Range("A3:A" & lastrow).SpecialCells(xlCellTypeVisible).Count
    End With
    
End Function
2
  • 3
    Add a sub Sub ApplyFilter : Call FilterX(ActiveSheet) : End Sub then assign shortcut to sub,
    – CDP1802
    Commented 19 hours ago
  • 1
    Remember to clear the error after your With statement: If Err.Number <> 0 Then Err.Clear. Otherwise you may get unintended side effects later.
    – GoWiser
    Commented 17 hours ago

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.