Jump to item in list based on first (and subsequent) letter (Excel 2003)

Bob88

New Member
Joined
Mar 21, 2019
Messages
2
Is there an option to jump through a list on a worksheet alphabetically in Excel 2003?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,884
Code:
By 'list' do you mean a sorted range of cells?
If yes:
  Is the size of the range fixed?
  Are there any blank lines in the range?
If no:
  Do you mean a listbox? or something else?
 

Bob88

New Member
Joined
Mar 21, 2019
Messages
2
Code:
By 'list' do you mean a sorted range of cells?

A list such as below (but mabe 200 or 300 rows).

[B]Name  Age[/B]
Alex      31
John     29
Rose     28
Sara     27

So I want to press the "R" key and jump to the first name beginning with "R" on the list.
If I then press "J" then jump to the first "J" on the list and so on.

Rgds
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,884
The code to jump to the first cell that contains the desired letter is not difficult.
The difficulty comes from how to trigger that code.
Excel has several events that could be used, but they would make it hard to use the worksheet for reasons other than finding that first matching name.
Also I don't know of a way to make Excel start processing after a single letter is pressed.

With those limitations, place the following code in the codepage of the sheet that contains the list.

The code currently will examine column A if you click on any cell in column A then show an input box that asks you to enter a letter and press return to find the first item in column A that contains the letter.

Creating the list with this code active will be hard since the code will trigger each time a different cell is selected

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Columns(1)) Is Nothing Then
    
        Dim sInput As String
        
        sInput = InputBox("Enter letter to search for")
        If sInput = vbNullString Then GoTo End_Sub
        
        On Error Resume Next 'In case desired letter is not present
        Application.EnableEvents = False 'So code won't trigger itself
        With Columns(1) '1 for column A, change number for other letters (26 = Z, etc.)
            .Find(What:=sInput & "*", LookIn:=xlFormulas, LookAt:=xlWhole, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Select
        End With
        Application.EnableEvents = True
        On Error GoTo 0
        
    End If

End_Sub:

End Sub

Another option for triggering would be to enter a single character into another cell.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top