ExcelBanter - Using autofilter in VBA

I'm still on xl2003 so I don't know about 2007/2010 but the autofilter
dropdown in 2003 is limited to (i think) 1000 unique entries and hence
even if you could (I don't think it's possible, though) access the
list it would still be rather useless for you because of the 1000 cap.

You can use advanced filter as there is an option to filter out only
Unique entries.

If you do it with a vba (and you want to go down the filter route as
oppose to for next loop) then you can have a look at this code that
gets the unique values and puts them into 2 dimensional array:

Sub ReadUniqueIntoArray()

Dim myArr As Variant
Const TargetCell As String = "G1" ' -- cell where the unique list
will be copied to - adjust as necessary
Const FilterRange As String = "A1:A14" ' -- adjust your filter
range as necessary

Range(FilterRange).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
TargetCell), Unique:=True ' Filters unique and copies the
result into the target column

myArr = Range(TargetCell, Cells(Rows.Count,
"G").End(xlUp).Cells).Value ' reads all the values into an array

End Sub

A.

On 25 Apr, 01:30, fslost wrote:
Hello,

I would like to know if it is possible copy the data in excel column to a
variable using the autofilter. I have a huge amount of data repeated in a
column, like:
18189
18189
18189
18189
18189
..
18190
18190
18190
18190
18190
..
18191
18191
* ...

And I just want the single data, without repetition, like:
18189
18190
18191
* ...

I know that it could be done with a "for" cicle, but it takes a lot of time
running, because the excel sheet have around of 60 000 lines of data.

So, the autofilter gives me the data without repetition right? can I get access
to that data and save them to a variable?

Thanks so much for helping.