get values from an AutoFilter

Question

I am using a ListObject that has AutoFilter on, and I would like the unique values from a given column for use in a form. Here are several ways to use vba to do this but you would need to have some way to catch edits to the table. Since AutoFilter is doing all this work anyway, I'm wondering if there is a way to get the values from the AutoFilter.

Can this be done? How?

Answer 1

I would like the unique values from a given column for use in a form.

To get unique value from a list, use a collection with OERN. You can then use that collection in your form

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Dim col As Collection, itm

    Set ws = Sheet1 '<~~ Change as applicable

    With ws
        '~~> Find last row of col A. Change to necessary column
        lRow = .Range("A" & .Rows.Count).End(xlUp).Rows

        For i = 2 To lRow '<~~ Starting from 2nd row
            On Error Resume Next
            If Len(Trim(.Range("A" & i).Value)) <> 0 Then _
            col.Add .Range("A" & i).Value, CStr(.Range("A" & i).Value)
            On Error GoTo 0
        Next i
    End With

    '~~> This is the unique list
    For Each itm In col
        Debug.Print itm
    Next
End Sub

So if your values in column are Apple, Oranges, Apple, Oranges, Oranges, Grapes then the final list will have Apple, Oranges, Grapes

Answer 2

The code that is here is fantastic for dealing with lots of common excel specific collection issues. Including several useful, fast ways of getting unique values from a range.