Quick Scroll ?

[Raz]

i have a very long spreadsheet with Names in the first column, (sorted
alphabeticaly) and corresponding other data to the right columns.

when I look for a name I have to scroll down forever to reach to the
appropriate name.
is there a quicker way to scroll.
for example If I am looking for John, I press J and it takes me to where
names starts with a J.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste the code below in. Enter a
letter in D1 and the first name beginning with that letter in column A will
be selected if there is one. Change D1 to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$D$1" Then
If Not IsNumeric(Target) Then
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If UCase(Left(c, 1)) = UCase(Target.Value) Then
c.Select
Exit Sub
End If
Next
End If
End If
End Sub


Mike

 

[Raz]

Thanks so much, awasome code, it works perfectly,

Just one concern,
do I need to save the file (workbook) with any special format (extention)
for this to work?

thanks again Mike.

Raz

 

[Ron de Bruin]

You use 2007 (use xlsm or xlsb or xls and not xlsx)

 

[Ashish Mathur]

Hi,

If the list is already sorted in ascending order, your effort can
significantly reduce by just applying an auto filter on the range. Then in
the filter drop down you can simply scroll to J or any other alphabet.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com