auto filter

[Guest]

Is there a way to get the old auto filter view back in excel 2007? I want to
be able to navigate the auto filter dropdowns via keyboard only. In older
versions of excel I would use alt+down to get the dropdown in the header. I
could then use arrow keys or type the first letter of the info I want to
filter. I used to be able to do this to cycle through data in the old excel
with this handy key combo… (alt+ down arrow)+(down arrow) +(enter) and repeat
over and over. This was very quick and useful. Is there a way to do this in
excel 2007?

 

[Peo Sjoblom]

Try alt + down arrow, down arrow until you get to select all, then press
space to deselect all items,
now type the first letter until you find the item, then space to select it
then enter.

You are definitely worse off when it comes to ease of use in this department
Of course the filter is more powerful now and you can for instance filter on
colours


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Guest]

How about this, Is there a shortcut to get the focus to the last checked box?
If so I could possibly use a 3rd party macro program to get that
functionality back.

 

[Roger Govier]

Hi Joe

Yes, its a bit of a pain in XL 2007, as it was when they introduced,
Sort Ascending, Sort Descending in XL2003.
As Peo says, there are many advantages to the changed Autofilter in
XL2007.

One way around your problem, could be to use the following even code
pasted into the sheet where your Filters are applied.
You need to insert a row above your header row containing the Autofilter
dropdowns.
In the code I used row 1, but you can change this to whatever you want.

Now you just type into row 1 what you want for the column, and as you
hit Enter, Tab or any arrow key, the filter becomes invoked. Press
delete on that cell, and the filter is cleared and all is visible again.

Typing a* will give everything starting with "a".
Typing a?t* will bring up artic and antic and so on.
Hope this helps to not only overcome your problem, but give you an even
faster way of working.

The code and methodology was inspired by a program I saw of Peter
Grebenik, from Brooke's University, Oxford (when we attended the XL User
Conference last year), to whom I am indebted for the idea.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rownum As Long, colnum As Long
Const testrow = 1 '<====Set this to the row above your filter
rownum = Target.Row
colnum = Target.Column
If rownum <> testrow Then Exit Sub
On Error Resume Next
If Cells(rownum, colnum).Value = "" Then
Selection.AutoFilter Field:=colnum
Else
Selection.AutoFilter Field:=colnum, Criteria1:=Cells(rownum,
colnum).Value
End If
On Error GoTo 0
End Sub

 

[Guest]

Hello Roger,
That idea seems interesting. I’m not proficient with excel macros so exuse
me if this seems obvious. I copied that text, went to excel and clicked
macros button. Typed a macro name and hit create. Pasted that text and got
two red lines which I interpreted as errors. I deleted your comment on one
line and removed some spaces on another and the red went away. I then clicked
save workspace assuming it would save this macro and I closed the visual
basic window. I inserted a row above my header (row 1). When I type on that
first row and hit enter or tab I don’t get the menu as you stated. When I
click macros I don’t see the name of the new macro (am figuring that’s
because that code starts off with private sub?) But when I go in to edit one
of my other macros I do see it. What do you think I'm missing here?

Here's what the code now looks like...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rownum As Long, colnum As Long
Const testrow = 1 '
rownum = Target.Row
colnum = Target.Column
If rownum <> testrow Then Exit Sub
On Error Resume Next
If Cells(rownum, colnum).Value = "" Then
Selection.AutoFilter Field:=colnum
Else
Selection.AutoFilter Field:=colnum, Criteria1:=Cells(rownum, colnum).Value
End If

 

[Roger Govier]

Hi Joe

The code needs to go into the relevant sheet, not into a regular code
module.
Right click on the sheet tab>View Code will open the VBE and it should
show that sheet name highlighted in the Explorer
pane of the VBE.
Paste the code directly into the code pane that is visible.

I have amended the code so there should be no false line wraps created
by your newsreader.
I have also added a line which placed the cursor back in the cell where
you have typed your value, so that if it is not what you want, you can
type delete immediately (without moving the cursor) to show all data
again.

I would delete the macro that you created, jut to tidy things up.


You do not have to apply any macro for it to operate, as it is a
Worksheet change event. As soon as the values on the sheet alter, the
macro fires up automatically. If the row number where the change occurs
does not match the number you have set as testrow, the macro ends.

If there is an autofilter on the cell below, then it sets it to the
value you have typed in.
If you have deleted the item in the cell, therefore it is null ("") then
the filter is removed.
If there is no Autofilter enabled for the column you are entering a
value, the On error resume next prevents the macro from crashing because
it can't find a filter, and it continues to the end.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim rownum As Long, colnum As Long
'Set this next value to the row above your filter
Const testrow = 1
rownum = Target.Row
colnum = Target.Column
If rownum <> testrow Then Exit Sub
On Error Resume Next
If Cells(rownum, colnum).Value = "" Then
Selection.AutoFilter Field:=colnum
Else
Selection.AutoFilter Field:=colnum, _
Criteria1:=Cells(rownum, colnum).Value
End If
Range(Target.Address).Activate
On Error GoTo 0
End Sub

 

[Guest]

Hi Roger, Thanks for all the help. I got it to work. I had no Idea what an
event code was until yesterday. That added functionality seems interesting
but I still want to figure out a way to enable that ‘flip filtering’ I
mentioned in the original post. If I could figure out a way to get the focus
to the last checked item in the drop down list I know I could program a third
party macro program to do the rest. Or maybe a third party program could do
it in a way I’m just not seeing. Like making use of event codes or some other
functionality in excel that I’m not aware of?

 

[Roger Govier]

Hi Joe

I'm not sure I am understanding you here.
Perhaps you could mail me a copy of your file with some more notes on
exactly what you are trying to achieve.
To mail direct, remove NOSPAM from my address.