Hi Keith,

I can't specifically answer your question and I will monitor the thread for
answers. However, the following could be used as a work around if you don't
get a better answer. It involves using a temporary worksheet for data but the
temp sheet can be added as required and then deleted.

Basically it is using AutoFilter for your column K value, Copy the visible
data to a temp location then AdvancedFilter for the unique list. Tested in xl
2002 and xl2007.

Sub Test()

With Sheet3
If .AutoFilterMode = False Then
.Range("A1").AutoFilter
Else
If .FilterMode Then
.ShowAllData
End If
End If
'Field 11 is column K
.AutoFilter.Range.AutoFilter _
Field:=11, Criteria1:="4"
End With

Sheets("Temp").Columns(1).Clear 'Any previous data

'Copy visible data to Temp sheet
Sheet3.AutoFilter.Range.Columns(1) _
.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Temp").Range("A1")

Sheet2.Columns(1).ClearContents 'Any previous data

'copy unique data to Sheet2
Sheets("Temp").Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheet2.Range("A1"), _
Unique:=True

'Turn off Autofilter
Sheet3.AutoFilterMode = False

End Sub

--
Regards,

OssieMac


"ker_01" wrote:

Currently, I am using:

Sheet3.Range("A1:A10000").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheet2.Range("A1"), _
Unique:=True

This generates a list of the unique values from my source list. While I can
get to my real end result through several additional steps, I suspect that
I'm underutilizing AdvancedFilter, and am hoping for a few pointers on how to
get it to return some more specific information.

Specifically,
(1) I do have a criteria that I'd like to use without placing it in a cell
range (Select unique values from A1:A10000 where the corresponding value in
Column K is '4'). It appears that I can put the value 4 in a cell and
reference it, but can I just use a variable which is =4? If so, what is the
appropriate syntax?
e.g.,
CriteriaRange:= (Sheet1.range(K:K).value = 4)

(2) Ultimately, what I really want is a count of the unique values that meet
my criteria, so can I use a Count against the returned range, without sending
it to a physical page location? Perhaps I can make a named range that
references an array instead of a range, and use that to generate a count in a
second step?

Initially I was using formulas to get my unique counts, but using a
sumproduct formula (actually, several dozen of them) against an array this
large was just taking too long, and initial tests with AdvancedFilter
indicated it would be much faster.

I appreciate any syntax hints with the AdvancedFilter, or even confirmation
on whether or not what I'm attempting is even possible with AdvancedFilter.

My backup option is to just loop the page and sort the data into a 2D array
(while keeping track of whether each value is unique), but AdvancedFilter
seemed like a more direct option (no loops, etc)

Thank you,
Keith