Filter on Active Cell

haftl31

New Member
Joined
Sep 20, 2005
Messages
12
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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:

Code:
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

Board Regular
Joined
Oct 18, 2006
Messages
100
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

New Member
Joined
Sep 20, 2005
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,451
Members
410,612
Latest member
MrACED
Top