VBA code. Filter data in table by clicking a cell.

[Americomski]

Hello,

I have a sample problem. Lets say my table range is A2:A50. In this column, each row has either A, B, C or D

Now lets treat cell B2 as the "A" button. I need a formula so that when I click cell B2, the table range A2:50 is filtered to only include rows that have the letter "A" in them.

Thank you.

 

[DanteAmor]

Hi and welcome to MrExcel!
The next option is with VBA code. I suggest you click on cell B1 instead of B2.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Address = "$B$1" Then
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    Range("A:A").AutoFilter 1, Target.Value
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.