Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using autofilter in VBA
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) |