How do I quickly move up or down list

[Carol]

I have a list with 4000+ items in alphabetical order.
Is there an easy way to move up or down to another beginning letter other
than scrolling? Say I am in the Z's & want to go up to the B's, then back
down to the W's, etc... Any help would be appreciated.

Thanks,
Carol

 

[Guest]

Have you tried using <Ctrl><f>?

 

[Guest]

Control+F will find <every> occurance of a letter...could be very cumbersome...

I would try the Tools>Filter>Auto-Filter. Then you could select to see ONLY
the "B's", etc. via the Custom>Begins With feature. Check "Auto-Filter" in
Excel Help for details on this feature.

Good Luck

 

[Carol]

Thanks anyway, but I found that more trouble then just scrolling.
I guess if I wanted 1 certain thing but I want an area. If I enter A
for find it brings up everyone including ones that aren't the first letter
of the first word.

Thanks again,
Carol

 

[Carol]

Thanks for your input.
Carol

 

[Guest]

One other option, working from the "Find" option...

Insert a blank column just to the left of your Names column. enter this
formula in the first row with names and copy down the entire range of names:

=LEFT(B2,1)&"xz"

This assumes your names are in col B and begin in row 2, adjust the B2
reference as needed.

Then, do a Find for Zxz (not likely to have other combinations of xz in your
list)

HTH

 

[Otto Moehrbach]

I don't know if you're interested in a VBA solution but, operationally, it
would be much simpler. Paste this macro into the sheet module of the sheet
that holds your data. As written, this macro assumes your data is in Column
A starting in A2. Cell A1 is the cell into which you type the letter you
want. Row 1 is frozen so that A1 is always visible regardless of the
scrolling of your data. That's it. Type "g" into A1 (without the quotes)
and your data will scroll (in an instant) and put the first entry that
starts with a "g" immediately below A1.
To access the sheet module for your sheet, right-click on the sheet tab,
select "View code" and paste this macro into that module. Please come back
if you need anything else. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngA As Range
Dim FoundCell As Range
Dim SLetter As String
If Target = "" Then Exit Sub
If Target.Address(0, 0) = "A1" Then
SLetter = [A1].Value & "*"
Set RngA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set FoundCell = RngA.Find(What:=SLetter, LookAt:=xlWhole,
After:=RngA(RngA.Count))
With ActiveWindow
.ScrollRow = FoundCell.Row
.ScrollColumn = 1
End With
End If
End Sub

 

[Carol]

Wow - Thank you.
Carol

 

[Carol]

Thank you again. I do have a worksheet I can use this in.
Carol

 

[David]

Otto Moehrbach wrote

I don't know if you're interested in a VBA solution but,
operationally, it would be much simpler. Paste this macro into the
sheet module of the sheet that holds your data. As written, this
macro assumes your data is in Column A starting in A2. Cell A1 is the
cell into which you type the letter you want. Row 1 is frozen so that
A1 is always visible regardless of the scrolling of your data. That's
it. Type "g" into A1 (without the quotes) and your data will scroll
(in an instant) and put the first entry that starts with a "g"
immediately below A1.
To access the sheet module for your sheet, right-click on the
sheet tab,
select "View code" and paste this macro into that module. Please come
back if you need anything else. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngA As Range
Dim FoundCell As Range
Dim SLetter As String
If Target = "" Then Exit Sub
If Target.Address(0, 0) = "A1" Then
SLetter = [A1].Value & "*"
Set RngA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set FoundCell = RngA.Find(What:=SLetter, LookAt:=xlWhole,
After:=RngA(RngA.Count))
With ActiveWindow
.ScrollRow = FoundCell.Row
.ScrollColumn = 1
End With
End If
End Sub

Suggestion: I would add On Error Resume Next after the Dim statements to
avoid error in the unlikely event nothing in the list starts with the
letter you type into A1.

 

[Don S]

I have a list with 4000+ items in alphabetical order.
Is there an easy way to move up or down to another beginning letter other
than scrolling? Say I am in the Z's & want to go up to the B's, then back
down to the W's, etc... Any help would be appreciated.

Thanks,
Carol

For quick and simple navigation, try END and UP(arrow) or DOWN(arrow).
That will take you to the next empty cell (or the last cell in a
list). Also ctl+home will take you to the top left available cell and
ctl+end will take you to the lowest right available cell.

Don S

 

[Otto Moehrbach]

Thanks for that, David. That's always a good thing to have. Otto

Otto Moehrbach wrote

I don't know if you're interested in a VBA solution but,
operationally, it would be much simpler. Paste this macro into the
sheet module of the sheet that holds your data. As written, this
macro assumes your data is in Column A starting in A2. Cell A1 is the
cell into which you type the letter you want. Row 1 is frozen so that
A1 is always visible regardless of the scrolling of your data. That's
it. Type "g" into A1 (without the quotes) and your data will scroll
(in an instant) and put the first entry that starts with a "g"
immediately below A1.
To access the sheet module for your sheet, right-click on the
sheet tab,
select "View code" and paste this macro into that module. Please come
back if you need anything else. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngA As Range
Dim FoundCell As Range
Dim SLetter As String
If Target = "" Then Exit Sub
If Target.Address(0, 0) = "A1" Then
SLetter = [A1].Value & "*"
Set RngA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set FoundCell = RngA.Find(What:=SLetter, LookAt:=xlWhole,
After:=RngA(RngA.Count))
With ActiveWindow
.ScrollRow = FoundCell.Row
.ScrollColumn = 1
End With
End If
End Sub

Suggestion: I would add On Error Resume Next after the Dim statements to
avoid error in the unlikely event nothing in the list starts with the
letter you type into A1.