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

[Kassie]

Have you tried using <Ctrl><f>?

"Carol" wrote:

> 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
>
>
>
>

[swatsp0p]

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

"Kassie" wrote:

> Have you tried using <Ctrl><f>?
>
> "Carol" wrote:
>
> > 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
> >
> >
> >
> >

[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

[swatsp0p]

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

"Carol" wrote:

> Thanks for your input.
> Carol
>
>
>

[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" <carolrob@[remove]austin.rr.com> wrote in message
news:V%eYd.63920$SE2.4851@fe2.texas.rr.com...
>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
>
>
>

[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.

--
David

[Don S]

On Fri, 11 Mar 2005 11:09:41 GMT, "Carol"
<carolrob@[remove]austin.rr.com> wrote:

>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
"David" <dturner4_1999@yahoo.com> wrote in message
news:u0x2ZAuJFHA.2640@TK2MSFTNGP09.phx.gbl...
> 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.
>
> --
> David

[BizMark]

There's actually a very simple way.

Use the normal 'Find' function but, for example, if you're searching
for entries that begin with 'U', then enter the following:

Find what: U*

Click: Options>>

Tick: Match Entire Cell Contents




That way, Excel will search ONLY for cells whose first letter is 'U' -
it won't find any cell that has 'U' anywhere in it.

Regards,
BizMark


--
BizMark