I have tested it Roger, but it shows blank, but when I deleted it returns me
back to the full list again. I appreciate if you could explain me more.

Thanks in advance.

Frank

"Roger Govier" wrote:

Hi Frank

I should have added, deleting the value in the cell in row 1, will
return you back to the full list again.

Also, the standard things like ??va would filter for advance, advantage
etc., *box* would find all lines with the word box anywhere within the
cell.

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Frank

You can only do this by selecting custom from the dropdown on the
column and choose begins with VA*


However the following worksheet code will do what you want, if you
insert a blank row above your Autofilter row.
As you enter any letter, or series of letters into a cell in row 1 of
the column you want to filter by, as soon as you press any key to
leave that cell, the list will be filtered for you.
You do not need to use the autofilter dropdowns at all.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'code created by Roger Govier, Technology 4 U

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 this value if necessary
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

This code need to be copied into the Sheet with your data.
Right click on your sheet tab
Choose View code to open the VB Editor
paste the code into the sheet pane.

Insert a blank row above your Autofilter, if one does not exist.
Change the Constant row number where shown, if it is not row 1

--
Regards

Roger Govier


"Frank Situmorang" wrote in message
...
Hello:

I have data auto filterin excel, when I wanted to find a record it
can only
jump into the first character. Is it possible to have an auto expland
like in
access data base??. For example I want to search in filter for Valve
when I
typed VA it will auto expand to Valve, varina, etc....

Thanks for any idea

Frank