Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick Scroll ?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick Scroll ?
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" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick Scroll ?
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 "Mike H" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick Scroll ?
You use 2007 (use xlsm or xlsb or xls and not xlsx)
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Raz" wrote in message ... 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 "Mike H" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick Scroll ?
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 "Raz" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scroll bar (control) doesn't scroll!! ??? | Excel Worksheet Functions | |||
Scroll bar control doesn't scroll !! ??? | Setting up and Configuration of Excel | |||
adding scroll bar to scroll on cell's content? | Charts and Charting in Excel | |||
Scroll horizontaly with mouse, create same system used to scroll . | New Users to Excel | |||
Scroll the screen view with the scroll bar | Excel Discussion (Misc queries) |