Storing Unique Values from Advanced Filter to an Array

autwake

New Member
Joined
Mar 21, 2018
Messages
1
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:

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:

Code:
    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?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Welcome to Mr Excel forum

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

Something like this
Code:
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.
 
Upvote 0
Which line of code causes the error?
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,187
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top