Neat Trick - Automatic Combo Box (Even Sorts And Removes Duplicates)

I found an extremely neat trick yesterday, competely by accident while making and testing a spreadsheet. I am so thrilled with this accidental discovery that I thought I would share it with the community, just in case some of you are not already aware.

I know there is not much of a chance of me teaching any of you experts anything new, but if in fact you were not aware of this neat little trick I would appreciate you replying letting me know, so I can get a little satisfaction about helping all of you who have helped me so much.

Go to the last open cell at the bottom of any column which contains text. There cannot be any empty cells in the column or this trick will only return the value of the cell directly above the cell you are in. There can be numeric values in some of the cells and text in others, but only the cells which contain text will show up in the combo box. There can even be formulas pointing to text values in other cells and those results will show up in the combo box. OK, now that your in the last open cell at the bottom of a column of contiguous data, hold down the alt key and hit the down arrow.

You should see a combo box with a sorted list of all the unique values. After the combo box appears you can let go of the alt key and then use your up and down arrows to navigate to the selection you want. Once your selection is highlighted just hit enter.

The other neat thing about this trick is that you don't have to be on the last open cell at the bottom of the column of data for it to work. You can be anywhere in a column of contiguous data and the combo box will display the items not only above your active cell, but below you as well.

As I said, I found this out by accident yesterday. I can't believe I didn't figure it out sooner as I use the Alt Down Arrow trick all the time to select values in a combo box, and I would have thought I would have mistakenly tried it on cells which did not contain data validation before yesterday, but I guess not.

By the way, it was Pjoaquin, or Paul as he is now known, who taught me how to use Alt Down Arrow in the first place to select items in a combo box back in a thread here in 2007. http://www.excelforum.com/excel-gene...elections.html

Sorry to get so excited about this discovery if this is common knowledge, but in case it isn't I thought I would pass it along. It sure beats hitting the up arrow a bunch of times to go to another cell and then copying, then hitting the down arrow a bunch of times and then pasting, or using your mouse to do all that.

Spence

Originally Posted by royUK

If it is the Pick from list feature, it's been available in all the versions of Excel that I have worked with.

I just did a quick search on "Pick From List" and sure enough, that is exactly what I am talking about. The description I just found states to right click and choose "Pick from drop down list..." from the context menu, which does the exact same thing that I was describing above with the Alt Down Arrow method.


Spence

This is a very cool trick and one that I think I can make great use of.

Is their a way to reference this list in VBA?
Can certain cells (header row etc) be ommitted from the referenced list?

I currently have a userform with a listbox populated with unique cell values in a column.
My one issue currently is it omits substring matches (so pay will be omitted if paycheck is in the column)