Getting autofilter list into a combobox...

[PorscheGT2]

Hello all,

Is it possible to get the criteria list for a particular autofilter to appear in a combo box?

TIA!

Marvin

 

[TonyJollans]

Hi PorscheGT2,

The short answer is no! There isn't any built-in way to access the list. There are a few ways to build a list of deduplicated values from a column, but you do have to write your own code for it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[PorscheGT2]

Hello Tony,

Thanks for your reply. One way I could think of doing it would be to sort the records, write a loop code and set a flag. Would it be possible though to do it without having to sort the records? Problem is, the users of the tool I'm working on will need to change names here and there, and would probably want their records arranged according to how they want them, so resorting alphabetically might not be logistically favorable for the users. Problem with Excel is the records you can create with it is meant to be static. Let me know if you have any other suggestions. Thanks again.

 

[TonyJollans]

Hi PorscheGT2,

One way to do it would be to use the Advanced Filter to copy unique values only to a new column (somewhere out of the way) which you could use as the source for your combo. I'm away now for the weekend but will post a bit more detail in a day or two if you want.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[PorscheGT2]

Hi Tony,

Sure, no rush, my deadline for this tool is on the 3rd of May. I'd be happy to look at any code you can produce and tailor/incorporate it into the tool. I would highly appreciate that.

Anyway, I've been trying to do a while, wend statement since this morning and no luck so far. My problem is, I haven't used the Do While, Loop and While, Wend statements extensively before so I'm having a hard time incrementing the search column and the paste column. It's not efficient, I know, but this was the only way I can think of doing it until you mentioned the advanced filter option.

Anyway, just post away when you get the time. I'll get notified by email once you do.

Thanks again for all your help.

 

[xlbo]

This should give you an idea:

Dim dataRng As Range, uniRngSt As Range, lRow As Long
lRow = Cells(65536, 1).End(xlUp).Row
[COLOR=green]'Set range to get unique values from[/color]
Set dataRng = ActiveSheet.Range("A2:A" & lRow)
[COLOR=green]'Set range to put unique values[/color]
Set uniRngSt = Sheets("Sheet1").Range("A1")
dataRng.AdvancedFilter xlFilterCopy, , uniRngSt, True

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question