Copy excel Filter's dropdown list into VBA array

[salmanm2]

Hi,

I've been trying for a while now to figure this out but with no joy.

My problem is dealing with large data sets from a factory log.

When inserting a filter into tables, the filter provides a drop down list of all the different entities in that column (which the user has the manual option to tick/untick his preferences). However, i need to store that list into an array for further work.

So far, to create a list of those entries i usually get it to put everything in ascending order and then run a loop from row_2 to row_max to store a figure every time the value changes. I know this works and its fast enough, but i'm sure there is a simpler way, which i'm hoping you guys may know of. As the drop down filter provides that list, i'm sure there is a VBA code to call up that list!!!


Would appreciate any feedback.

Thanks a mill

Mustafa

 

[Andrew Poulsom]

Welcome to the Board.

This shows you how to create a collection containing the unique values:

http://spreadsheetpage.com/index.php/tip/filling_a_listbox_with_unique_items/

 

[salmanm2]

Thanks Andrew,

This works perfectly, however i was still looking to save that collection as an Array. i should be able to just change that section to an array with a counter. right?

Salman

 

[salmanm2]

Ok, i just tried it with an array, didn't work, but i tried using the collection as an array, and it works like a treat, thanks guys. great site by the way. Cheers

Mustafa