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
rngFilter.AutoFilter Field:=iColumnNumber, Criteria1:=arrSelectedArray, Operator:=xlFilterValues
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)
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
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
I'm sorry I can't share my sheet because that would be pointless, and because it's too big and contains company data.
Can anybody suggest some code that will help with this?
Cheers
Bookmarks