Accessing the unique possible values from an autofilter

[mikecroom]

I have set up some VBA to set up a table with auto-filters (excel 2007)

Now I want to access the list of possible values for one of the columns - as shown in the drop-down when you set the filter manually.

Is there an object which provides this? I can't find one. I am looking under .AutoFilter.Filters

Or do I need to access the range and calculate the unique values myself?

Mike

 

[pgc01]

Hi

You must calculate them yourself.

2 quick ways

- using the worksheet: use the Advanced Filter to copy the unique values to to another range

- just vba: load a Dictionary object with the values

 

[venkat1926]

copy autofiltered data in another sheet and then do advanced filter

sheet1 trivial example

HDNG DATA
A 1
S 2
D 1
D 4
S 2
A 2
A 4
S 2
D 2

filtered data for S copied in sheet 2 as follows

HDNG DATA
S 2
S 2
S 2

now if you make advanced filter of sheet2 with uniuqe rows only and copy to range E1 you will get in sheet 2 E and F range will be

Excel Workbook
	E	F
1	HDNG	DATA
2	S	2
Sheet2

 

[mikecroom]

Thanks all. I am working with relatively small data sets so used a code snippet I found.
Shame I couldn't get the Excel autofilter to tell me - it does "know"

Code below - sorry no attribution:

 'returns an array of unique values within a specified range
Private Function UniqueValues(InputRange As Range)
    Dim cell As Range
    Dim tempList As Variant: tempList = ""
    For Each cell In InputRange
        If cell.Value <> "" Then
            If InStr(1, tempList, cell.Value) = 0 Then
                If tempList = "" Then tempList = Trim(CStr(cell.Value)) Else tempList = tempList & "|" & Trim(CStr(cell.Value))
            End If
        End If
    Next cell
    UniqueValues = Split(tempList, "|")
End Function