Filter on Active Cell

[haftl31]

Is there a way to quickly filter on the active cell?

For example, if I have a list of names that has been autofiltered. Is there a shortcut key that will show me only those cells that contain the name in the active cell without having to go up to the filter and scroll down to find the relevant name?

thanks

 

[Damon Ostrander]

Hi Haftl31,

Here's an example of how to do this. Let's say the autofilter is in cell D1 for column D, and the cell that you want to control the autofilter is B1. The following code added to the worksheet's code module will automatically perform the autofiltering when a filter value is entered in B1:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$B$1" Then
      [D1].AutoFilter Field:=1, Criteria1:=[B1].Text
   End If
End Sub

To place this code in the worksheet's code module, right-click on the worksheet's tab, select View Code, and paste this code into the Code pane.

Keep Excelling.

Damon

 

[Wolfshead]

Hi
You can customise your toolbar so that it contains the button for autofilter. It looks like a filter paper with an equal sign next to it. It filters based on the currently selected entry.

Wolfshead

 

[haftl31]

Thank you both. Wolfshead, that's exactly what I was looking for. Do you know if there is a keyboard shortcut for that button. If not, I can record a macro and assign one.

Thanks again. Very helpful.