Moving through alphabetical list

[Len Silva]

My project is a table with an alphabetical list of names in column A. Now that the list has grown to about 400 lines, scrolling through it is getting to be more of a chore.
What would be perfect would be to just enter the first letters and land on the name, just like Windows Explorer.
I know that I can use text filter, but looking for something simpler.

Thanks,

[dflak]

One of the issues you will have is that change events only take place after the contents of the buffer are written (which happens when you press ENTER or TAB or select another cell), so if you are looking for real-time-as-I-type placement. It's not going to happen. Well, it could happen but you have to place Excel into a special mode which is more work than advantage.

What we could do is give you a capability where you type the beginning* of what you want and either click or a button, or use a change event when you leave the cell and it will set the text filter for you.

Better still, you should be able to use wildcards like J*Smith* which will get you Joe Smith, Jane Smithers and any other matches. Let us know if this meets your needs.

[MarvinP]

Hi Len,

Simply typing won't trigger what you want. I've created a workbook with lots of names and then added 3 VBA routines to accomplish what I think you want. The first VBA routine will do an Advanced Filter on your names. The other two are behind the worksheet and are triggered by events. The first is when you double click on the yellow cell (E1) and it will clear the Advanced Filter. The last is triggered when you change the text in the yellow cell. It will then do what I think you want.

See the attached. I hope you can figure out what I did and helps with your problem.