How to perform an automated AutoFilter sort

[Jeff Smith]

I have an analysis spreadsheet 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

 

[Myrna Larson]

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

 

[Jeff Smith]

Thank you for this. I will try to make it do the job.

sincerely

Jeff Smith

 

[Myrna Larson]

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

 

[Myrna Larson]

If you want the unique product ID's, there is a way that uses built in Excel
functions, but it's not via a property of the AutoFilter object.

You could apply the Advanced Filter: First select *just* column E, then
Data/Filter/Advanced and check Unique Records and Filter in place. Then copy
the data in column E to another area. You could sort that new column if
desired.

You can code this in VBA: you can check out the AdvancedFilter method of the
Range object, but it may be quicker to turn on the macro recorder and do it
manually, then modify the code.

 

[Jeff Smith]

Myrna,

I've been working on the code and went back to newsgroup to see if any
updates. Thanks for these two updates - I will follow through on your
suggestions.

sincerely

Jeff Smith

 

[Jeff Smith]

Myrna,

This tip has worked extremely well. I now have a macro that produces a
listing of unique productsin AE3:AE203 (200 unique records is conservative).

Does this now help make my goal more achievable?
Also how do I specifiy the autofilter from and to so that this can automate
the task.

I rally appreciate your help here.

sincerely

Jeff Smith

 

[Myrna Larson]

I guess I don't really understand what your final goal is. In the code you
started with, you were, AIR, turning the AutoFilter on. I don't know if that
is really needed. Can you explain more about what happens when you process the
record?

Assuming you do need to filter, in the mean time, in the VB Editor, press F1
for help and search for AutoFilter. That will give you information about how
to work with the AutoFilter from VBA.

 

[Jeff Smith]

Hello again and thanks for staying with this. I appreciate your help.

The test data is entered progressively and sequentially by date. The number
of products is up to about 200. Each record has about 15 columns of test
data about various properties tested.

Presently, I select a product using autofilter and a macro copies the data
relating to that product into a temporary worksheet called "Desktop". There
is a reporting module "Analysis" that does a lot of statistical measurements
based on the unique population. The analysis report has been used for
several years and is excellent. Over time I have decided I need a summary
report that contains the main indicators only. Any problems can be picked
up by a quick scan and problem products zeroed in on for printing out the
comprehensive report.

I have already developed the macros that copies the key performance
indicators. I presently have to start at product A, look at the report and
select the copy to summary macro button. This macro positions the selected
cells in the top of the list (Row2, after the header), then copies it to the
bottom. The first record would then be in row 3 being the first blank row
in the defined range and so on until I have finished manually selecting all
the products. Each time I do this the macro completely deletes the data in
row 2 ready for the next operation. As far as the report is concerned it is
simply a space between the headers and the tabulated data.

I now have the summary of unique products (thanks to you). It seems to me I
could set screenupdating to false and press one button that uses the product
code at the top of the unique products list as the variable criteria for
performing the filtered sort which is then repeated until all unique product
records are processed.(each time this is done the KPI's are automatically
building (already done) in the report summary. This process ceases when the
last unique product code in the list of products has been processed.

If I could speak to the PC I'd say do an autofilter using criteria in the
list AE3:AE203 from the top of the list until the first blank cell is found.

I'm not a bad spreadsheeter. My work tool is excellent but it just needs
improving in efficiency and reduction of paper reports (and colour toner).
I lack the VBA skills to define the variables in AE3:AE203 and how to say
"do until". I'm stumped :- ) If I can get over this hurdle my work will be
to click a macro button that will refresh the unique list (just in case a
new product gets added)[done], sort the list [done], automate the filter
sort [not done] and produce the summary [done].

I hope this helps. I am really grateful for your help.

sincerely

Jeff Smith

 

[jeradklingen]

Here is my code, i am getting an error (type mismatch) on line:

If Not Application.Match(Product, Rng, 0) = R Then


I know its a little sloppy, im a beginner, you can read th
description.


Sub FilterCriteria()


' This section filters the column by uniqe vender number and
' Copys the results of the filter to a new sheet named
' the value of Product (which is the current Vender #)
' Then it saves the sheet as a new file and attaches it to
' an email, which is then sent to a contact named the value
' of the variable "Product", and repeats untill done

Dim Product As String
Dim R As Long
Dim Rng As Range

' this grabs uniqe vender numbers makes them = to product.
Set Rng = Range(Range("b2"), Range("b2").End(xlDown))
For R = 1 To Rng.Rows.Count
Product = Rng.Cells(R).Value

If Not Application.Match(Product, Rng, 0) = R Then GoTo gameover:

'selects the master sheet with all the data
Sheets("Sheet1").Select

'filters by vender number
Range("B1").Select

Selection.AutoFilter Field:=2, Criteria1:=Product
'Copys what was filtered
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

'Adds a sheet and names it the value of Product and pastes
'what was copied on master sheet1
Sheets.Add
ActiveSheet.Name = Product
ActiveSheet.Paste

'saves a worksheet as a new file, named value of Product
Sheets(Product).Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\cog4181\My Documents\overdueROmac\
& Product, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

'Returns to master sheet
Sheets("Sheet1").Select

Set myOlApp = CreateObject("Outlook.Application")
Set myitem = myOlApp.CreateItem(olMailItem)

Set myattachments = myitem.Attachments

'creates some stupid object thing
Set obj = CreateObject("Scripting.FileSystemObject")
'checks to see if the files exists, if not, goto gameover

If Not obj.FileExists("C:\Documents and Settings\cog4181\M
Documents\overdueromac\" & Product & ".xls") Then GoTo gameover:

'adds attachment and makes a caption "Overdue ro list"
myattachments.Add "C:\Documents and Settings\cog4181\M
Documents\overdueromac\" & Product & ".xls", _
olByValue, 1, "Over Due RO List"
myitem.Display

Set myRecipient = myitem.Recipients.Add(Product)

' if not in address book, Close the mail item
If Not myRecipient.Resolve Then

myitem.Close
GoTo gameover:
End If

'add that person assigned to product
myitem.Recipients.Add Product

myitem.Subject "OVERDUE RO LIST"

' send the damn EMAIL
myitem.Send

Sheets("Sheet1").Select

gameover:

Next R

End Su