2007 Autofilter worse than 2003 Autofilter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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..
 
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
 
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
 
I don't think you're missing anything. There are more clicks required in
the simple case. The tradeoff is that you can now filter on any number of
elements in the list, not to mention the oft-requested (not by me<g>) color
filter.

--
Jim
| 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?
| >
| >
| > > 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..
| >
| >
| >
 
I am afraid you are probably right. Thanks for at least helping me be more
certain that there is no other way.

Jeff
 
OK, you meant what I thought you meant. You are correct, it does take
more mouse clicks; I just haven't found this to be so much of an
impediment. In fact, I like the ability to apply multiple items
beyond what custom would allow in 2003.

The old functionality is only preserved in PivotTable page fields, as
far as I know. It is also improved from 2003 by allowing multiple
items to be selected, but for that area single-item selection is the
default.
 
Thanks Jim. You are probably right and I will have to go find a way to use
that feature. Sounds like I will have to get used to a 100% increase in my
work steps for a previously handy tool.

Jeff
 
Thanks Ilace.
I'll get used to it. And probably start braging on the ease of doing more
complicated filters instead of the Custom way in the past.
I appreciate the response.
 
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 Tab>View code>Paste

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
 
Back
Top