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

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

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

Thanks Roger, I have read your 2 Notes, now I am going to try it, I will
inform you if I can suceed. I found it what a helpful people all of you who
live in other sides of the globe

Greetings from Jakarta, Indonesia, South East Asia.

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

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

Thank you very much Roger, It works infact. It is because I did not put the
wild character such as "VA**, I just typed VA, so it showed blank. It works
perfectly now. My question is how many rows that it can handle. Is it
possible for 65,000 rows?

Thanks a lot

Frank

"Frank Situmorang" wrote:

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

Hi Frank

Glad you got it running.

Typing VA on its own, would be looking for items beginning with VA (not
case sensitive). Typing VA** would do exactly the same. *VA would look
for items where any word contained the letters VA.
The code keeps the cursor in the same cell to make it easy to add more
characters to your search.
So you could enter V for all entries staring with V, then VA which would
limit it to those starting VA, then VAL etc. to narrow down the
selection

The code is merely setting the parameters for Autofilter, without having
to use the steps of selecting the dropdownCustombegins with etc.
On Excel versions up to 2002, the dropdown on Autofilter was quite
short, but it has expanded with later versions making the choosing of
options slightly more laborious, that's why I wrote the code.

The capabilities therefore, are still those of Autofilter itself. Yes,
it will deal with the full list of 65536 rows of XL versions to 2003,
but there is an inbuilt limitation, that Autofilter will not display
more than 1000 rows for any filter applied.

Debra Dalgleish has some methods for getting around this limitation on
her site at
http://www.contextures.com/xlautofilter02.html#Limits

--
Regards

Roger Govier


"Frank Situmorang" wrote in message
...
Thank you very much Roger, It works infact. It is because I did not
put the
wild character such as "VA**, I just typed VA, so it showed blank. It
works
perfectly now. My question is how many rows that it can handle. Is it
possible for 65,000 rows?

Thanks a lot

Frank

"Frank Situmorang" wrote:

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

Thanks Roger for your more explanation on using * character and how we narrow
down the selection. I appreciate if you could explain on how we can modify
the VBA to have the returned selection into sequence on the Unit Price
Column, coz we want to know the lowest price of supplier's quotations.

Again thanks in advance, we in developping country are benefited from you in
advanced country.

Frank

"Roger Govier" wrote:

Hi Frank

Glad you got it running.

Typing VA on its own, would be looking for items beginning with VA (not
case sensitive). Typing VA** would do exactly the same. *VA would look
for items where any word contained the letters VA.
The code keeps the cursor in the same cell to make it easy to add more
characters to your search.
So you could enter V for all entries staring with V, then VA which would
limit it to those starting VA, then VAL etc. to narrow down the
selection

The code is merely setting the parameters for Autofilter, without having
to use the steps of selecting the dropdownCustombegins with etc.
On Excel versions up to 2002, the dropdown on Autofilter was quite
short, but it has expanded with later versions making the choosing of
options slightly more laborious, that's why I wrote the code.

The capabilities therefore, are still those of Autofilter itself. Yes,
it will deal with the full list of 65536 rows of XL versions to 2003,
but there is an inbuilt limitation, that Autofilter will not display
more than 1000 rows for any filter applied.

Debra Dalgleish has some methods for getting around this limitation on
her site at
http://www.contextures.com/xlautofilter02.html#Limits

--
Regards

Roger Govier


"Frank Situmorang" wrote in message
...
Thank you very much Roger, It works infact. It is because I did not
put the
wild character such as "VA**, I just typed VA, so it showed blank. It
works
perfectly now. My question is how many rows that it can handle. Is it
possible for 65,000 rows?

Thanks a lot

Frank

"Frank Situmorang" wrote:

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

Hi Frank

As I said, the code is merely offering a slightly quicker way of using
XL's inbuilt filtering functionality, it is not doing the filtering, and
therefore there is little point in trying to built a sort into the code.

If you sort your table by Unit price Ascending first, then apply the
filter, the quotation will be in ascending order of price
--
Regards

Roger Govier


"Frank Situmorang" wrote in message
...
Thanks Roger for your more explanation on using * character and how we
narrow
down the selection. I appreciate if you could explain on how we can
modify
the VBA to have the returned selection into sequence on the Unit Price
Column, coz we want to know the lowest price of supplier's quotations.

Again thanks in advance, we in developping country are benefited from
you in
advanced country.

Frank

"Roger Govier" wrote:

Hi Frank

Glad you got it running.

Typing VA on its own, would be looking for items beginning with VA
(not
case sensitive). Typing VA** would do exactly the same. *VA would
look
for items where any word contained the letters VA.
The code keeps the cursor in the same cell to make it easy to add
more
characters to your search.
So you could enter V for all entries staring with V, then VA which
would
limit it to those starting VA, then VAL etc. to narrow down the
selection

The code is merely setting the parameters for Autofilter, without
having
to use the steps of selecting the dropdownCustombegins with etc.
On Excel versions up to 2002, the dropdown on Autofilter was quite
short, but it has expanded with later versions making the choosing of
options slightly more laborious, that's why I wrote the code.

The capabilities therefore, are still those of Autofilter itself.
Yes,
it will deal with the full list of 65536 rows of XL versions to 2003,
but there is an inbuilt limitation, that Autofilter will not display
more than 1000 rows for any filter applied.

Debra Dalgleish has some methods for getting around this limitation
on
her site at
http://www.contextures.com/xlautofilter02.html#Limits

--
Regards

Roger Govier


"Frank Situmorang" wrote in message
...
Thank you very much Roger, It works infact. It is because I did not
put the
wild character such as "VA**, I just typed VA, so it showed blank.
It
works
perfectly now. My question is how many rows that it can handle. Is
it
possible for 65,000 rows?

Thanks a lot

Frank

"Frank Situmorang" wrote:

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

Thank you very much Roger, your help is great.

Frank

"Roger Govier" wrote:

Hi Frank

As I said, the code is merely offering a slightly quicker way of using
XL's inbuilt filtering functionality, it is not doing the filtering, and
therefore there is little point in trying to built a sort into the code.

If you sort your table by Unit price Ascending first, then apply the
filter, the quotation will be in ascending order of price
--
Regards

Roger Govier


"Frank Situmorang" wrote in message
...
Thanks Roger for your more explanation on using * character and how we
narrow
down the selection. I appreciate if you could explain on how we can
modify
the VBA to have the returned selection into sequence on the Unit Price
Column, coz we want to know the lowest price of supplier's quotations.

Again thanks in advance, we in developping country are benefited from
you in
advanced country.

Frank

"Roger Govier" wrote:

Hi Frank

Glad you got it running.

Typing VA on its own, would be looking for items beginning with VA
(not
case sensitive). Typing VA** would do exactly the same. *VA would
look
for items where any word contained the letters VA.
The code keeps the cursor in the same cell to make it easy to add
more
characters to your search.
So you could enter V for all entries staring with V, then VA which
would
limit it to those starting VA, then VAL etc. to narrow down the
selection

The code is merely setting the parameters for Autofilter, without
having
to use the steps of selecting the dropdownCustombegins with etc.
On Excel versions up to 2002, the dropdown on Autofilter was quite
short, but it has expanded with later versions making the choosing of
options slightly more laborious, that's why I wrote the code.

The capabilities therefore, are still those of Autofilter itself.
Yes,
it will deal with the full list of 65536 rows of XL versions to 2003,
but there is an inbuilt limitation, that Autofilter will not display
more than 1000 rows for any filter applied.

Debra Dalgleish has some methods for getting around this limitation
on
her site at
http://www.contextures.com/xlautofilter02.html#Limits

--
Regards

Roger Govier


"Frank Situmorang" wrote in message
...
Thank you very much Roger, It works infact. It is because I did not
put the
wild character such as "VA**, I just typed VA, so it showed blank.
It
works
perfectly now. My question is how many rows that it can handle. Is
it
possible for 65,000 rows?

Thanks a lot

Frank

"Frank Situmorang" wrote:

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

Email ID:

When you use the AutoFilter command in Microsoft Excel, the AutoFilter
list may not display all the unique visible items in a column as
expected. the limitation of the amount of entries in the AutoFilter list
is 1,000. If you have more than 1,000 unique items in the list, only the
first 1,000 items appear.


There is no way to increase this limit in the current versions of Excel;
it is hard-wired. If you want to do more complex filtering, you will
need to use the advanced filtering tools in Excel. (They aren't that
hard to use, and can provide some very interesting ways of examining
your data.)


If you prefer to use AutoFilter, you might look at how your data is
organized in your table. For instance, let's say you have a huge list of
customer transactions, and you want to filter the data by customer name.
The trouble is, you have more than 1,000 unique customers, so you can't
see all the customer names in the AutoFilter drop-down list.

The solution is to add another column, perhaps just to the left of your
customer name column. Use a formula in the column that pulls out the
first letter of the customer's last name. You now have a list of single
alphabetic letters. It is much easier to AutoFilter on this column; all
26 letters appear quite nicely in the drop-down list. Select a letter,
and this filters your data so that only those customers whose last names
begin with that letter are displayed. You can then use the drop-down
list in the customer name column to select from all the customers in the
pared-down list.

- Yogesh

*** Sent via Developersdex http://www.developersdex.com ***