Re: Auto_expand on excel

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <frank.situmorang@xxxxxxxxxxxx> wrote in message
news:180021D2-75F9-4A4C-98F5-C7FD1EB679BB@xxxxxxxxxxxxxxxx
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 dropdown>Custom>begins 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" <frank.situmorang@xxxxxxxxxxxx> wrote in message
news:50C2D440-26EA-4E00-941C-2D20332A07E4@xxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • RE: Test if Column B is autofiltered
    ... If numCells iLastRow Then ' This determines if there is a filter on B ... Michael Arch. ... to run IF the Autofilter is on for Column B, AND a selection has been made in ... Autofiltered for any selection in Column B before it will continue to ...
    (microsoft.public.excel.programming)
  • Re: Problem with autofilters
    ... I have been able to reproduce what you say with Autofilter. ... You will now have an Excel Table, ... Filter on 3 as before, fill down as required and it will behave correctly. ... overwrites also the rows with 1 and 2 that are hidden in selection. ...
    (microsoft.public.excel)
  • Re: Problem with autofilters
    ... You will now have an Excel Table, with Autofilter automatically applied to the header row. ... Filter on 3 as before, fill down as required and it will behave correctly. ... If I want to fill all the rows for the selection in other columns Excel overwrites also the rows with 1 and 2 that are hidden in selection. ...
    (microsoft.public.excel)
  • Re: Problem with autofilters
    ... You will now have an Excel Table, with Autofilter automatically applied to the header row. ... Filter on 3 as before, fill down as required and it will behave correctly. ... overwrites also the rows with 1 and 2 that are hidden in selection. ...
    (microsoft.public.excel)
  • Re: Return Value from a Filtered List
    ... look for 'FL0007 - Highlight AutoFilter Headings' ... would like to title based on a user's selection from a filtered list. ... I am having trouble referencing the cell using dynamic titles ... because it never returns the value in the same position (the filter ...
    (microsoft.public.excel.worksheet.functions)