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]

How about using the Filter function, in addition to the Unique and Sort functions?

Aa = Application.Transpose(Evaluate("sort(unique(filter(" & TblName & "[" & HEADERCAT1 & "],INDEX(" & TblName & ",0,3)=""Devices"")))"))  'Array of ITEMS

Hope this helps!

 

[nemmi69]

Excellent, last bit what would I need to put in place of "Devices" to select all?

 

[nemmi69]

Modified it to below where TblItemDrop1 = "Devices" and get type mismatch

Aa = Application.Transpose(Evaluate("sort(unique(filter(" & TblName & "[" & HEADERCAT1 & "],INDEX(" & TblName & ",0,3)=" & TblItemDrop1 & ")))"))  'Array of ITEMS

 

[nemmi69]

The context behind this is there are 3 dropdowns
1st selects "Category"
2nd selects "Manufacturer"
3rd selects Models

so the 1st should restrict the 2nd to only that category, the 2nd should restrict the 3rd to only the manufacturer.

Clicking the 1st should reset 3nd and 3rd, clicking 2nd should reset 3rd

 

[Domenic]

Modified it to below where TblItemDrop1 = "Devices" and get type mismatch

Aa = Application.Transpose(Evaluate("sort(unique(filter(" & TblName & "[" & HEADERCAT1 & "],INDEX(" & TblName & ",0,3)=" & TblItemDrop1 & ")))"))  'Array of ITEMS

You need to enclose TblItemDrop1 within quotes...

Aa = Application.Transpose(Evaluate("sort(unique(filter(" & TblName & "[" & HEADERCAT1 & "],INDEX(" & TblName & ",0,3)=""" & TblItemDrop1 & """)))"))  'Array of ITEMS

Hope this helps!

 

[nemmi69]

Thanks, but seems to return only 1 item not a list

 

[Domenic]

Can you post a small sample of your data, along with your expected results?

Also, just to be sure, can you post the exact code that you're using?

 

[nemmi69]

Starting to think I have the wrong approach. Ultimately the table will need updating from the selection. So I was thinking that best filter the table as options are made. This is fine but I would need to make the arrays (Aa, Bb and Cc) see the filtered list not the whole. I know with normal ranges you can specify only visible (SpecialCells(xlCellTypeVisible) ). Can that be applied in this case Aa?

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

 

[nemmi69]

Well solved it another way, I created a function

' Filter2Array collects all visible in array. If NoUnq is not True will collect unique. If NoSrt not true will sort alphabetically
Function Filter2Array(ByRef RngVisible As Range, Optional ByRef NoUnq As Boolean, Optional ByRef NoSrt As Boolean) As Variant
Dim RngCell As Range, TmpArr() As Variant, RCnt As Long
 
RCnt = 0
'Loop through visible range and populate the array
For Each RngCell In RngVisible
    ReDim Preserve TmpArr(RCnt)
    TmpArr(RCnt) = RngCell
    RCnt = RCnt + 1
Next RngCell
' Unique
If NoUnq = False Then
    TmpArr = Application.Unique(TmpArr, True) ' True as it is in row form
End If
' Sort
If NoSrt = False Then
    TmpArr = Application.Sort(TmpArr)
End If

Filter2Array = TmpArr
End Function

The sub I used to test it

Sub CtrlDropDowns()
TmpRng As Range

If WbPrime Is Nothing Then
    Initialise
End If
Set TmpRng = TblId.ListColumns(HEADERCAT1).DataBodyRange.SpecialCells(xlCellTypeVisible)
Aa = Filter2Array(TmpRng)
WsPrime.Range(Cells(12, 1).Address, Cells(12 + UBound(Aa) - 1, 1).Address).Value = Application.Transpose(Aa)
End Sub