Excel Autofilter drop-down list items

[kingofcardiff]

Using VBA, I need to retrieve the list of items(?) from an Excel autofilter drop-down list. With this, I will programatically select from and set the criteria..

For instance, if I am autofiltering a list where team names are in the filtered column, I want to get the list of teams that are included in the list.

Numbered items (item(1), item(2)) don't seem to do the trick. I'm limited to two criteria (criteria1, criteria2).

Help!

 

[DaleWatson123321]

King,

When I see a description such as yours where you refer to key words like "retrieve" and "criteria", it seems clear that you have not yet reached that level of Excel's "Data Filter" that you indeed require. That is "Advanced Filter".

Excel users who explore Data - Filter - Advanced Filter, and then attempt to use "Copy to another location" to copy the data to a SEPARATE sheet will run into a bug in the form of an ERROR message that says this is NOT possible. NOT so - indeed it IS possible.

Another false ERROR message will tell users that "criteria" cannot be placed on a SEPARATE sheet. Again NOT so - it IS possible.

There are also quirks regarding how the "criteria" must be set up. For example, when a formula is used as part of the criteria, a field name must NOT be used. However, if a label is used as part of the criteria, then a field name MUST be used.

Notwithstanding the above "minefield", Excel's "database functionality" is EXTREMELY powerful and useful. This includes database formulas, and the ability to "selectively" extract data to a SEPARATE sheet based on specific criteria that YOU specify. AND, you CAN use MULTIPLE criteria.

I've developed a variety of example database files for various Tek-Tips users, and can email you one or more of these files to "point you in the right direction".

These files include VBA code (attached to macro buttons) that: 1) Extracts the data to a SEPARATE sheet, and 2) Sorts and formats the data. Some notes are also included that explain some of the steps, quirks, etc.

Another option, would be for you to email me your file, and I would insert the VBA code and return it. If you have any sensitive data, just replace it with fictitious data that still reflects the type of data your dealing with.

You can reach me at my HOME address on the weekend.

For ANYONE ELSE who can utilize such files, also feel free to ask.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[kingofcardiff]

Actually, I can use the advanced filter. I can get the a unique list from a named range and put it on a separate worksheet. Then I'll have the list I want. BUT! I want to skip the intermediate step.

I was hoping to have the autofilter setup so that I programmatically retrieve (and populate an array) the list of criteria from the drop-down box Excel generates with the autofilter.

I'll see if I can put together a file and send it to you. Your offer is quite generous!