Hi Jeff

If your Autofilter is in row 1, insert a new blank row at row 1.
Copy the following code, then right click the sheet TabView codePaste

Now enter any value in row 1 of the relevant column, and the data will be
filtered by that value.
e.g. cat* will filter on anything beginning with cat. *cat* will filter by
anything containing cat. c?t* will find anything beginning with cat, cit,
cot, cut
All of the numeric operators work for columns with numeric data.

The advantage is that the filtered column is highlighted, and it shows what
you have filtered that column on.
deleting an entry in row 1 for any column, removes the filter for that
column

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rownum As Long, colnum As Long
Dim tblname As String, mylist As Object
'Set this next value to the row number above your filter
Const testrow = 1
rownum = Target.Row
colnum = Target.Column
On Error Resume Next
If Target.Count 1 Then
Rows(testrow + 1).Select
ActiveSheet.ShowAllData
GoTo cleanup
End If
If rownum < testrow Then GoTo cleanup

If Val(Application.Version) < 11 Then GoTo earlyversion

Set mylist = ActiveSheet.ListObjects
If mylist.Count Then
tblname = mylist(1).Name
End If

If Cells(rownum, colnum).Value = "" Then
If mylist.Count Then
mylist(tblname).Range.AutoFilter Field:=colnum
GoTo cleanup
End If
Selection.AutoFilter Field:=colnum
Else
If mylist.Count Then
mylist(tblname).Range.AutoFilter Field:=colnum, _
Criteria1:=Cells(rownum, colnum).Value
GoTo cleanup
End If

Selection.AutoFilter Field:=colnum, _
Criteria1:=Cells(rownum, colnum).Value
End If
GoTo cleanup

earlyversion:
If Cells(rownum, colnum).Value = "" Then
Selection.AutoFilter Field:=colnum
Else
Selection.AutoFilter Field:=colnum, _
Criteria1:=Cells(rownum, colnum).Value
End If

cleanup:
Range(Target.Address).Activate
On Error GoTo 0
End Sub


--
Regards
Roger Govier



"jsky" wrote in message
...
I am afraid you are probably right. Thanks for at least helping me be more
certain that there is no other way.

Jeff

"Peo Sjoblom" wrote:

I belive you have to do this, they have applied the same way you select
items from a pivot table to the autofilter now. There are benefits
though,
you can now filter on colours etc


--


Regards,


Peo Sjoblom


"jsky" wrote in message
...
Sorry you don't understand, you must not use it as often as I did.
Very simply, when i turned on Autofilter of a spreadsheet with several
columns, a small drop down arrow appears in column heading. The same
drop
down arrow appears now BUT in earlier versions when i click on it, the
box
which "drops down" is simply filled with the unique data elements of
that
column of the spreadsheet, plus a {blanks} choice, and Custom. All I
have
to
do is move my mouse over the unique data element and click on it and
VOILA
the spreadsheet hides all the rows that do not contain that unique data
element.

Contrast that with what I find I have do to now with Excel 2007 (unless
of
course I need to be taught some new trick which I admit I don't know.).
In
Excel 2007, I turn on Autofilter, chose the dropdown box of the column
which
I'd like a unique filter to be applied, and instead of floating over
the
list
of unique items and clicking on the one I want, I have to FIRST uncheck
all
of the items by clicking on "(Select All)". Then I have to go put a
check
mark next to the unique data element which I want filtered. Then I
have
to
click on OK. That is 3 clicks when it used to just be 1.
Help. What am I missing? Is there an easier way?

Jeff

"iliace" wrote:

How did it work in the old version that you prefer and can't do in
2007?


On Oct 30, 1:11 pm, jsky wrote:
The autofilter feature for Excel 2007 is far more cumbersome to use
than
previous versions. Now I have to deselect all checked boxes (i know
you can
click select all to unclick) and then scroll down to the one item I
want to
filter on. THat is like 2-4 MORE clicks than I had to do before.

Is there any way to bring up the old way it worked? This is a step
backwards..