Hi all
I have a need to
1. Filter a sheet,
2. Extract unique values from a column, and
3. Populate a listbox (on another sheet) with the unique values.
I can filter using VBA, but I don't have the foggiest on how to extract unique values from a filtered column into an array.
The filter
With this, the idea is that I can use a listbox(1) to select the items I want to filter. This will filter the sheet. Then I collect the unique values from another column which I can then use to populate listbox(2)rngFilter.AutoFilter Field:=iColumnNumber, Criteria1:=arrSelectedArray, Operator:=xlFilterValues
then listbox(3),(4)... and so on.
It should be noted that I am using this code to extract unique items, but I don't now how to make it work with a filtered list
I'm sorry I can't share my sheet because that would be pointless, and because it's too big and contains company data.Dim dic As Object, vData As Variant, i As Long Dim Array1 As Variant 'Y is my filtered results column Set WkSh = Worksheets("MySheet") 'Create a dictionary object Set dic = CreateObject("Scripting.Dictionary") LRfilt = WkSh.Range("Y" & Rows.Count).End(xlUp).Row dic.CompareMode = vbTextCompare 'Pass the data to a variant array to speed processing vData = WkSh.Range("Y2:Y" & LRfilt) 'Loop For i = LBound(vData) To UBound(vData) If vData(i, 1) <> "" Then dic(vData(i, 1)) = Empty Next i 'Pass the unique keys to a 0 based array Array1 = dic.keys 'Confirm content of array For i = 0 To UBound(Array1) Debug.Print Array1(i) Next i
Can anybody suggest some code that will help with this?
Cheers
Bookmarks