Data validation list by letter

[Cam40933]

I have used Data validation in excel to create a list of Car vehicles. I would however like to type in the first letter of the vehicle and the list to go to the vehicles beginning with this letter. I have so many vehicles it would save so much time scrolling up and down.

Your answers would be much appreciated.

 

[moxy85]

You could easily just use autofilter, click "Custom"

rule is Begin with "Letter"

click "ok"

Done.

Again the same feature could be done in VBA so that you could enter "letter" into a cell and the list populate with that letter easily.

I hope that helps.

 

[MickG]

One option:-
Change "B1" in code to Validation cell address.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim Dn As Range
If Target.Address(0, 0) = "B1" And Len(Target) = 1 Then
  Set rng = Range(Mid$(Range("B1").Validation.Formula1, 3, Len(Range("B1").Validation.Formula1) - 2))
For Each Dn In rng
    If Left$(Dn, 1) = Target Then Target = Dn
Next Dn
  End If
End Sub

Mick