none
Dynamic Filters at Top Row RRS feed

  • Question

  • Hello Excel Pros!!!

    I really hope someone could help me with this please~~~~

    I would like to have text filters in the top row of my table so as i type a text, the table gets filtered. I would normally click on the filter button, then click Text Filter, then select Contain (or else) and then type text, which takes a lot of clicks and a lot of time. But I'm sure there is a way to keep the filters at the top of a table or create them using macro or something smart?

    For example, as i type letters "oes" in D1, it filters the table to show only the rows that contain those letters in column D. And the same in E1. And so on. But I'd be happy with filters just for columns D and E. 


    something like this

    Hope i'm making sense. Help me please ~~~

    Thank you!

    Jay


    Thursday, May 24, 2018 9:49 AM

Answers

  • Hello jay.nz,

    Please update the code like this

        If Target.Cells.Count = 1 And Not Intersect(Target, Worksheets("Sheet1").Rows(1)) Is Nothing Then
        Dim ws As Worksheet
        Set ws = Worksheets("Sheet1")
        lastRowIndex = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        ws.Range("A2:M" & lastRowIndex).AutoFilter Field:=Target.Column, Criteria1:="=*" & Target.Text & "*", _
                Operator:=xlAnd
        End If

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jay.nz Wednesday, May 30, 2018 9:11 AM
    Wednesday, May 30, 2018 8:57 AM

All replies

  • Hi Jay,

    Based on your description, your problem can be achieved by using VBA code, I will help you move this thread to Excel for Developer forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, May 25, 2018 8:05 AM
  • Hello jay.nz,

    You could use WorkSheet_Change event to trigger code to filter after changing value in first row.

    Here is the sample VBA code.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 And Not Intersect(Target, Worksheets("Sheet1").Rows(1)) Is Nothing Then
    Worksheets("Sheet1").Cells.AutoFilter Field:=Target.Column, Criteria1:="=*" & Target.Text & "*", _
            Operator:=xlAnd
    End If
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 29, 2018 8:34 AM
  • Thank you for the code, Terry!

    I'm not sure why, but for some reason, i'm getting an error. You know one of those Debug errors. Trying to make sense of the code for myself and tweak it while googling and youtubing, but no joy so far. 

    If you dont mind, a sample file can be viewed here.

    Regards,

    Jay

    Wednesday, May 30, 2018 8:43 AM
  • Hello jay.nz,

    Please update the code like this

        If Target.Cells.Count = 1 And Not Intersect(Target, Worksheets("Sheet1").Rows(1)) Is Nothing Then
        Dim ws As Worksheet
        Set ws = Worksheets("Sheet1")
        lastRowIndex = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        ws.Range("A2:M" & lastRowIndex).AutoFilter Field:=Target.Column, Criteria1:="=*" & Target.Text & "*", _
                Operator:=xlAnd
        End If

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jay.nz Wednesday, May 30, 2018 9:11 AM
    Wednesday, May 30, 2018 8:57 AM
  • WORKS PERFECTLY! Thank you so much, Terry!

    Now, i'm slowly starting to understand what command means what action. :-)

    Thank you!

    Wednesday, May 30, 2018 9:11 AM