Re: How to perform an automated AutoFilter sort

From: Myrna Larson (anonymous_at_discussions.microsoft.com)
Date: 07/29/04 

Date: Wed, 28 Jul 2004 22:56:46 -0500

Please let me know if it works.

The MATCH function checks that this is the first occurence of the product ID
in the column. If it is, it will process it; if not, it is skipped since it
was processed when encountered in a row above.

BTW, if you want to generate the list you see in the dropdown, you'd need to
use this code but, instead of processing the row, add the ID to an array. (If
you want to process them in alphabetical order, then you also need a sort
routine to sort the array.) Then send that array to the routine that does the
"real work".

On Thu, 29 Jul 2004 15:30:45 +1200, "Jeff Smith" <anonymous@newgroup.com>
wrote:

>Thank you for this. I will try to make it do the job.
>
>sincerely
>
>Jeff Smith
>
>
>
>"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
>news:h3pgg0hj130nh38bu9lv3v1snafb5a4pot@4ax.com...
>> There's no way to access the list that you see in the dropdown
>> programmatically.
>>
>> But you could perhaps use something like this in your code. I assume there
>are
>> headers in row 1, and product numbers are in column E, with no blank
>cells.
>>
>> Then
>>
>> Dim Product As String
>> Dim R As Long
>> Dim Rng As Range
>>
>> Set Rng = Range(Range("E2"), Range("E2").End(xlDown))
>> For R = 1 to Rng.Rows.Count
>> Product = Rng.Cells(R).Value
>>
>> If Application.Match(Product, Rng, 0) = R Then
>> 'put code here to process this product
>>
>> End If
>> Next R
>>
>>
>>
>>
>> On Thu, 29 Jul 2004 14:11:12 +1200, "Jeff Smith" <anonymous@newgroup.com>
>> wrote:
>>
>> >I have an analysis spread*** that is essentially a database of test
>> >results for the various products monitored. The number of products can
>vary
>> >as new ones are introduced and the convention of determining product
>codes
>> >are inconsistent. They are however always unique withing the product
>range
>> >listing.
>> >
>> >At this time I have an autofilter set up that displays products in a
>> >drop-down filter list. Excel automatically detects and displays a list
>of
>> >all the unique products in the drop-down list. I do the analyses one at
>a
>> >time by progressively selecting from the top through to the bottom of the
>> >list.
>> >
>> >I have been trying to work out (for a long time) how to automate this
>> >process so I can click a macro button and let a macro perform all of the
>> >filtered sorts. I already have a macro that captures the (manually
>> >selected) analysed data and drops it into a summary report.
>> >
>> >I have recorded the macro and the code is:
>> >
>> >Selection.AutoFilter Field:=3, Criteria1:="Unique_ProductID_A"
>> >Selection.AutoFilter Field:=3, Criteria1:="Unique_ProductID_B"
>> >Selection.AutoFilter Field:=3, Criteria1:="Unique_ProductID_C"
>> >until....
>> >Selection.AutoFilter Field:=3, Criteria1:="Unique_ProductID_ZZ"
>> >
>> >Defined products are contained in Column E.
>> >
>> >What I do not know how to do is to use VBA to determine or define the
>"top
>> >of the list", select the next unique product (second in the list) and
>> >progress until the last unique record (bottom of the list) is found.
>> >
>> >This much desired task is beyond my VBA skills and it may be that some
>kind
>> >person sees this as a straightforward "do until" routine and can offer
>some
>> >advice, suggestions, guidelines or similar code used for a similar
>problem
>> >in the past.
>> >
>> >Thanks in anticipation.
>> >
>> >regards
>> >
>> >Jeff Smith
>> >
>>
>