Filtering and a table by double clicking on the header.

[Eggy66]

Say for example I have this table:

How can I make it so that when I double click on the header "verb", it filters the table to show only the rows that contains X in that header's column. And when I double click on the header again the filters gets removed, and if I clicked on another header while the table is already filtered, it gets removed first before filtering it.

I used this VBA code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Application.Intersect(Target, Range("A1:D1")) Is Nothing Then
      Cancel = True
      If Me.AutoFilterMode Then
         Me.AutoFilterMode = False
      Else
         Me.Range("A1:D1").AutoFilter Field:=Target.Column, Criteria1:="X"
      End If
   End If
End Sub

It works, however it doesn't remove the filter when I double click on the same header again, and it doesn't remove previous filters before filtering another column.

Any help would be much appreciated!

 

[Eggy66]

Managed to solve it with the help of ChatGPT.
Here if anyone needs it:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim tbl As ListObject
    Dim filterRange As Range
    
    Set tbl = Me.ListObjects("Table1") ' Change "Table1" to the name of your table
    
    ' Check if the double-clicked cell is in the header row
    If Not Intersect(Target, tbl.HeaderRowRange) Is Nothing Then
        ' Determine the column index of the double-clicked header
        Dim colIndex As Long
        colIndex = Target.Column - tbl.HeaderRowRange.Column + 1
        
        ' Get the filter range for the column
        Set filterRange = tbl.ListColumns(colIndex).DataBodyRange
        
        ' Toggle filter
        If filterRange.Parent.AutoFilter.FilterMode Then
            filterRange.Parent.AutoFilter.ShowAllData
        Else
            Me.Range("A1:D1").AutoFilter Field:=Target.Column, Criteria1:="?"
        End If
        
        Cancel = True ' Prevent default behavior of double-click
    End If
End Sub