Generate sorted unique list from filtered table

[nemmi69]

Using the code below I still get the entire list under the header even though a filter is applied for 'Devices'. Trying to find how to get just the filtered list.

TblId.Range.AutoFilter Field:=3, Criteria1:= "Devices"
 ' 

Aa = Application.Transpose(Evaluate("sort(unique(" & TblName & "[" & HEADERCAT1 & "]))")) 'Array of ITEMS

 

[Domenic]

Here's possibly another way...

Dim rng
Set rng = TblId.ListColumns(HEADERCAT1).DataBodyRange

Aa = Application.Transpose(Evaluate("SORT(UNIQUE(FILTER(" & rng.Address & ",SUBTOTAL(3,OFFSET(" & rng.Address & ",ROW(" & rng.Address & ")-MIN(ROW(" & rng.Address & ")),0,1)))))"))

Although your function will give you more flexibility.

Cheers!