• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter As You Type

Qseeme

New Member
Hi,

This is my first post.

I came across the link below made by Chandoo. It is essentially what I am looking for - except I would like additional code to filter additional other columns (Example attached).

Tab1 = Original working file with 1 column
Tab2 = What I would like - currently 3 columns.


Source = https://chandoo.org/wp/filter-as-you-type-excel/

Thanks.
--------------------------------------------------------------------------------------------
Mod Edit: Thread moved to appropriate forum.
 

Attachments

  • filter-as-you-type.xlsm
    25.9 KB · Views: 3
Last edited by a moderator:
So, how do you want the filter to behave?

Search in all 3 columns as you type and filter all 3 columns?
Or some other logic?

What should happen if "7" is found in both 2nd & 3rd columns?
 
Correct. 1 column would be searched and narrowed down further assuming other columns had additional search criteria (either numeric or text) - all as you type.
 
By the way - Keep in mind that each column search is independent of each other. So in your example where there "What should happen if "7" is found in both 2nd & 3rd columns?" - 7 would have to be entered in both the 2nd and 3rd column.
 
Why do you have 3 separate autofilter range? Instead of using single table?

As best practice, never have multiple ranges (or tables) in same sheet that has it's own filter range. This creates multitude of issues.

Does it matter if all 3 columns reside in same table?
 
Yes, would prefer it that way. Currently using drop down filters on all columns, but is cumbersome and also one is never sure what is actually being filtered. So essentially looking for something that would do the same thing, is visually better and can be entered rapidly avoiding the drop down filters that I have always hated. I also do not want to go the pivot table route (which is fine for me), but others are too afraid to use and cannot be convinced. Unless of course, you have a different approach. Tens of thousands of lines are involved. I would ideally (but probably too difficult) to have another row underneath that would filter with > < for those that are numeric. Just a wish list.
 
Last edited by a moderator:
Something like below?

In Worksheet module (for "WOULD LIKE")
Code:
Private Sub TextBox1_Change()
    FilterAsSearch
End Sub
Private Sub TextBox2_Change()
    FilterAsSearch
End Sub
Private Sub TextBox3_Change()
    FilterAsSearch
End Sub

Sub clearFilter()
    [O4] = "": [P4] = "": [Q4] = ""
    ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
End Sub

Then in standard module (ex: Module1).
Code:
Sub FilterAsSearch()
With ActiveSheet
For i = 1 To 3
    If Len(.Cells(4, 15 + i - 1)) > 0 Then
        If Not IsNumeric((.Cells(4, 15 + i - 1).Value2)) Then
            .ListObjects(1).Range.AutoFilter Field:=i, Criteria1:= _
                "=*" & .Cells(4, 15 + i - 1).Value & "*", Operator:=xlAnd
        Else
            .ListObjects(1).Range.AutoFilter Field:=i, Criteria1:= _
                .Cells(2, 3 + i - 1).Value & .Cells(4, 15 + i - 1).Value, Operator:=xlAnd
        End If
    Else
        .ListObjects(1).Range.AutoFilter Field:=i
    End If
Next
End With
End Sub

See attached.
 

Attachments

  • filter-as-you-type.xlsm
    38.6 KB · Views: 11
Back
Top