Long alphabetical list, macro to select first item per letter?

[Rerock]

I have our product list (several thousand items), which is alphabetized by manufacturer; say $E$10:$E$365

I would like to have macro buttons that will jump to the first item in that list which starts with the corresponding letter.

Example: I push the "H" button, and would select cell D183 becuase that is the first product which starts with the letter H (even though there may be 50 products that start with H).

The challenge for me, is the size of the list changes monthly so I can't just create a macro to take me to a specific cell every time.

Also, I would prefer to not have to enter a letter in a different cell to use as a reference

Thoughts/ ideas? I've attached an example.

Thanks!

[tigeravatar]

Rerock,

Does it have to be a macro? The autofilter feature can do this rather easily. Also the Find feature (ctrl+f)

[StevenM]

Please Login or Register  to view this content.

[Leith Ross]

Hello Rerock,

Welcome to the Forum!

You can assign this same macro to each button. It uses the button's letter to find the first cell that begins with that letter. This can be used in Excel 2003 or 2007. The match is not case sensitive. Copy this code to a module in your workbook's VBA project.

Please Login or Register  to view this content.

[yogananda.muthaiah]

chk this out in this below line

Set Btn = ActiveSheet.Shapes(Application.Caller)

[Rerock]

Hello Rerock,

Welcome to the Forum!

You can assign this same macro to each button. It uses the button's letter to find the first cell that begins with that letter. This can be used in Excel 2003 or 2007. The match is not case sensitive. Copy this code to a module in your workbook's VBA project.

Wow, AMAZING!!!! Absolutely NO WAY I could have EVER figured that out on my own.

Edit: Works in 2010 as well.