PDA

View Full Version : how to grab filter list data



leal72
08-13-2014, 12:32 PM
I'm trying to find a way to use a macro to list each unique number from the Auto Filter list. Want the macro to output the data as follows,
0.06 to G1
0.25 to G2
0.44 to G3
ect.

12096

JKwan
08-13-2014, 01:08 PM
give this a go

Sub UniqueItems()
Dim MyArray As Variant
Dim dDictionary As Object
Dim Index As Long
MyArray = ActiveSheet.[C1].Resize(ActiveSheet.[C65536].End(xlUp).Row)
Set dDictionary = CreateObject("scripting.dictionary")
For Index = 1 To UBound(MyArray)
dDictionary(MyArray(Index, 1)) = dDictionary(MyArray(Index, 1)) + 1
Next
ActiveSheet.[G1].Resize(dDictionary.Count) = Application.Transpose(dDictionary.Keys)
Set dDictionary = Nothing
End Sub

leal72
08-13-2014, 03:12 PM
Thank you very much! Worked perfectly.