How can I select an item in a drop down ?

  • Thread starter Thread starter sonnynvangie
  • Start date Start date
S

sonnynvangie

I have a long list in my drop down sorted in alpha. How can I select a
letter that will bring me to the list starting with that letter?
 
From a posting by Howard Kittle............................

You can get close to that. Sort your validation lists and at the beginning
of the A's enter a single A, do the same for the beginning of the B's and
all the way through to the Z's.

Click in the cell that has the drop down validation and type in the letter
of the list you desire... say you type in an O. DO NOT hit enter... leave
the cell in the edit mode (still selected) and now hit your down arrow.

Should take you to the O which is the top of the O list of selections.


HTH
Regards,
Howard
...............................................

If you have a very long list with many alphas you may want to sort the list
and add the first letter via macro.

Copy your list to column A...........adjust code if another column

Sub Alpha_Isert()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

FirstRow = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If Left(Cells(iRow, "A").Value, 1) <> _
Left(Cells(iRow - 1, "A").Value, 1) Then
Rows(iRow).Insert
With Cells(iRow, "A")
.Value = UCase(Left(Cells(iRow + 1, "A").Value, 1))
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP
 
Back
Top