Hi,

I searched for this but could not find a way to solve this.

I use the AutoFilter a lot, when the Custom AutoFilter is started it
always starts with the option "equals" in the drop down menu but I
want the Custom Auto Filter dialog box to start with the option
"contains" .

This will save a lot of time for me. Excel gurus pls help.

Thanks,
root

And if MS made those dropdown boxes just a bit bigger, you wouldn't have to
scroll down to get to Contains, either! (My personal irritant!)

I don't think you can change this behavior, but you can cheat a little.

If I want to filter a field so that it contains "root", I can use
Equals
and type
*root*

If you go back into that custom option, you'll see that excel knows that you
want contains.

And
root*
is the same as "begins with" root

*root
is the same as "ends with" root


wrote:

Hi,

I searched for this but could not find a way to solve this.

I use the AutoFilter a lot, when the Custom AutoFilter is started it
always starts with the option "equals" in the drop down menu but I
want the Custom Auto Filter dialog box to start with the option
"contains" .

This will save a lot of time for me. Excel gurus pls help.

Thanks,
root

--

Dave Peterson

Thanks Dave. I wish there was a way we can re-order that drop down
menu. Your idea very much works, just need to type 2 more chars!

On Jul 5, 12:32 am, Dave Peterson wrote:
And if MS made those dropdown boxes just a bit bigger, you wouldn't have to
scroll down to get to Contains, either! (My personal irritant!)

I don't think you can change this behavior, but you can cheat a little.

If I want to filter a field so that it contains "root", I can use
Equals
and type
*root*

If you go back into that custom option, you'll see that excel knows that you
want contains.

And
root*
is the same as "begins with" root

*root
is the same as "ends with" root

wrote:

Hi,

I searched for this but could not find a way to solve this.

I use the AutoFilter a lot, when the Custom AutoFilter is started it
always starts with the option "equals" in the drop down menu but I
want the Custom Auto Filter dialog box to start with the option
"contains" .

This will save a lot of time for me. Excel gurus pls help.

Thanks,
root

--

Dave Peterson

I find typing those *'s is quicker than mousing to the Contains option.

wrote:

Thanks Dave. I wish there was a way we can re-order that drop down
menu. Your idea very much works, just need to type 2 more chars!

On Jul 5, 12:32 am, Dave Peterson wrote:
And if MS made those dropdown boxes just a bit bigger, you wouldn't have to
scroll down to get to Contains, either! (My personal irritant!)

I don't think you can change this behavior, but you can cheat a little.

If I want to filter a field so that it contains "root", I can use
Equals
and type
*root*

If you go back into that custom option, you'll see that excel knows that you
want contains.

And
root*
is the same as "begins with" root

*root
is the same as "ends with" root

wrote:

Hi,

I searched for this but could not find a way to solve this.

I use the AutoFilter a lot, when the Custom AutoFilter is started it
always starts with the option "equals" in the drop down menu but I
want the Custom Auto Filter dialog box to start with the option
"contains" .

This will save a lot of time for me. Excel gurus pls help.

Thanks,
root

--

Dave Peterson

--

Dave Peterson

Hi Dave

You can avoid using the dropdowns altogether with the following code.
This works for all version of XL from 97 through to 2007, including
dealing with the List and Table objects of 2003 and 2007.

Insert 1 row above your autofilter row, and just type your root*, *root,
r?t etc in the cell above the column you want to filter.
Deleting the entry in the cell takes you back to showing all data.

For the benefit of the OP I have included instructions at the end on how
to copy this to the Worksheet code area.
The code assumes that the row number where you are going to type data is
Row 1, but you can amend to suit.

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 '<------- Change if required
rownum = Target.Row
colnum = Target.Column
If rownum < testrow Then Exit Sub

If Target.Count 1 Then
On Error Resume Next
Rows(testrow + 1).Select
ActiveSheet.ShowAllData
On Error GoTo 0
GoTo cleanup
End If

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

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

On Error Resume Next
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
Range(Target.Address).Activate
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

For the benefit of the OP, right click on the tab of the sheet with your
Autofiltered data, and copy the code into the large white code pane
area. Press Alt +F11 to take yourself back to the worksheet.

--
Regards

Roger Govier


"Dave Peterson" wrote in message
...
I find typing those *'s is quicker than mousing to the Contains option.

wrote:

Thanks Dave. I wish there was a way we can re-order that drop down
menu. Your idea very much works, just need to type 2 more chars!

On Jul 5, 12:32 am, Dave Peterson wrote:
And if MS made those dropdown boxes just a bit bigger, you wouldn't
have to
scroll down to get to Contains, either! (My personal irritant!)

I don't think you can change this behavior, but you can cheat a
little.

If I want to filter a field so that it contains "root", I can use
Equals
and type
*root*

If you go back into that custom option, you'll see that excel knows
that you
want contains.

And
root*
is the same as "begins with" root

*root
is the same as "ends with" root

wrote:

Hi,

I searched for this but could not find a way to solve this.

I use the AutoFilter a lot, when the Custom AutoFilter is started
it
always starts with the option "equals" in the drop down menu but
I
want the Custom Auto Filter dialog box to start with the option
"contains" .

This will save a lot of time for me. Excel gurus pls help.

Thanks,
root

--

Dave Peterson

--

Dave Peterson