Auto-filter by cursor location

[whatisit]

Hi All

I use auto-filter a lot on very long spreadsheets. I would like to
know if it's possible to somehow filter quickly by the value in the
cell that is the current cursor location.

So, say the current cell has HELLO in it. At the moment I move my
pointer to the drop down arrow, click, enter 'h' to position
approximately, page down, down, down till I get close, then select
HELLO. This probably doesn't seem like much, but using only the
keyboard (e.g. when the laptop is on my lap) it's rather tedious.
Since I do this a lot, I'd love to find a shortcut that says: "Filter
to show cells that match the current cell value".


Thanks.

 

[Earl Kiosterud]

You could use this macro, perhaps assigning it to a keyboard shortcut.

Sub FilterBySelection()
Selection.AutoFilter Field:=Selection.Column - Selection.CurrentRegion.Column + 1,
Criteria1:=ActiveCell.Value
End Sub

 

[shabutt]

Please look into Fast Filter excel file from
"http://www.contextures.com/excelfiles.html#Filter" which is great. Another
way is to add "AutoFilter" command to the excel menu which only filters
quickly by the value in the cell that is the current selected cell.

 

[whatisit]

Thanks for the tips, guys.

 

[whatisit]

Can't seem to get this to run. I don't think I mentioned that I'm
using Office 2000, if that matters.

It doesn't seem to like the comma at the end. If I remove that it runs
without error but doesn't do anything.

 

[Earl Kiosterud]

You have to have set up the range as an autofilter first. You'll see the dropdown arrows if
you've done that.

 

[whatisit]

You have to have set up the range as an autofilter first. You'll see the dropdown arrows if
you've done that.

You've been generous with your time, but there's some element that's
missing - perhaps some aspect of my setup that you can't see.

It fails both with and without the comma at the end, even if I set up
a *special* auto-filtered range to test it. Normally, I just
autofilter everything, because I want to be able to filter anything
and everything at whim.

Thanks anyway.

 

[Denise]

I would set up an advanced filter to accomplish this task.
http://www.contextures.com/xladvfilter01.html#ApplyAF