Storing Unique Values from Advanced Filter to an Array

[autwake]

Hi All,

I am a VBA newbie and want to find the unique values from a column and store them to an array.

I found this thread: https://www.mrexcel.com/forum/excel-questions/67144-filter-list-vba-array.html, and am trying out the post by dk with the following code:

Sub CopyToRange()
Dim Array1

Range("A1:A5474").AdvancedFilter Action:=xlFilterCopy, Unique:=True, CopyToRange:=Range("A6000")
Array1 = Range("A6000:A" & Range("A65536").End(xlUp).Row).Value
Range("A6000:A65536").Clear 'Get rid of the filtered data
 
End Sub

For my project, I adapted the code to be:

    Sub Find_Uniques()
 
    Dim Array1


    Sheets("Map").Range("GM2:GM60000").AdvancedFilter Action:=xlFilterCopy, Unique:=True, CopyToRange:=Sheets("Map").Range("A6000")
    Array1 = Range("A6000:A" & Range("A65536").End(xlUp).Row).Value
    Range("A6000:A65536").Clear 'Get rid of the filtered data
 End Sub

However, I keep getting run-time error '1004': "This can't be applied to the selected range. Select a single cell in a range and try again."

Any thoughts or guidance?

 

[Marcelo Branco]

Welcome to Mr Excel forum

I would use a Dictionary object to get the unique values - much faster than Advanced Filter.

Something like this

Sub aTest()
    Dim dic As Object, vData As Variant, i As Long
    Dim Array1 As Variant
    
    'Create a dictionary object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    'Pass the data to a variant array to speed processing
    vData = Range("GM2:GM60000")
    '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
    'Just to check...
    MsgBox Array1(0)
End Sub

If you're not familiar with the Dictionary object take a look at
https://excelmacromastery.com/vba-dictionary/
http://www.snb-vba.eu/VBA_Dictionary_en.html

Hope this helps

M.

 

[Fluff]

Rather than copying/pasting/clearing why not use a dictionary? https://excelmacromastery.com/vba-dictionary/

 

[Norie]

Which line of code causes the error?