1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by Qseeme, Jul 11, 2018.

  1. Qseeme

    Qseeme New Member

    Messages:
    7
    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.

    Attached Files:

    Last edited by a moderator: Jul 11, 2018
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935
    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?
  3. Qseeme

    Qseeme New Member

    Messages:
    7
    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.
  4. Qseeme

    Qseeme New Member

    Messages:
    7
    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.
  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935
    ... so you want 3 separate search field? Not just the one then.
  6. Qseeme

    Qseeme New Member

    Messages:
    7
    Correct.... Sorry that I was not explicit with that.
  7. Qseeme

    Qseeme New Member

    Messages:
    7
    Any ideas / solutions ? Can it be done with minimal effort ?
  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935
    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?
  9. Qseeme

    Qseeme New Member

    Messages:
    7
    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: Jul 12, 2018
  10. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935
    Something like below?

    In Worksheet module (for "WOULD LIKE")
    Code (vb):
    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 (vb):
    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.

    Attached Files:

    Thomas Kuriakose and Qseeme like this.
  11. Qseeme

    Qseeme New Member

    Messages:
    7
    POWERFUL. Well done. Thanks Chihiro.
    Last edited by a moderator: Jul 12, 2018

Share This Page