Re: How to perform an automated AutoFilter sort

From: Jeff Smith (anonymous_at_newgroup.com)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 15:30:45 +1200

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
> >
>