Keyboard shortcut to get to the next changing text in a column

[Helal]

is there a keyboard shortcut to reach to the next changing text in a column. For example:
if I have the following data in column A:
X
X
X
Y
Y
Y
Y
Z
Z
Z

How to do I get from X at top (A2) to first instance of Y and then first instance of Z with keyboard shortcut?

Thanks,

[Ken Puls]

Nope. You could write some VBA code to do it, but no built in keyboard shortcut.

If the list is long though, you could use the Find feature. To do this:
-Highlight the entire column
-Press CTRL+F
-Type in the letter you want and click OK

That should take you there.

[Helal]

Thank you Ken...but I think I saw that somewhere few months ago and failed to preserve it. the find solution works if I know what the letter is but in some cases and with more than 700K data lines, I like to be able to just get to the next text when column is sorted.

[Ken Puls]

Try this...

• Open Excel
• Press Alt+F11 to open the Visual Basic Editor
• In the Project Explorer (on the left), find your workbook and expand it
• Right click "Microsoft Excel Objects" and choose Insert -> Module
• Paste the following in the code pane that opens up:

Option Explicit

Sub FindNextLetter()
    Dim rngToSearch As Range
    Dim lLetter As Long
    
    Set rngToSearch = Range(ActiveCell.Address & ":" & ActiveCell.End(xlDown).Address)
    lLetter = Columns(Left(ActiveCell, 1)).Column + 64
    
    Do Until lLetter = 90
        lLetter = lLetter + 1
        On Error Resume Next
        rngToSearch.Find( _
            What:=Chr(lLetter), _
            After:=ActiveCell, _
            LookIn:=xlFormulas, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False).Activate
        If Err.Number <> 0 Then
            Err.Clear
        Else
            Exit Do
        End If
    Loop
    
    If lLetter = 90 Then MsgBox ("Sorry, there are no higher letters in your list!")
End Sub

• Close the Visual Basic editor
• Press Alt+F8
• Make sure "FindNextLetter" is highlighted
• Click Options
• In the Shortcut key field, enter a Capital J
• Click OK

Now select any cell in your column of letters and press CTRL+SHFT+J. You should be taken to the next highest letter.

You'll need to save your workbook in a Macro Enabled format if you're using Excel 2007 or higher (an xlsm file)

If you need to use this on several workbooks, we'll need to go through some slightly different steps to install it.

Hope it helps,

[Simi]

Ken I am curious if you can do something with a formula on this then use the ctrl + up arrow or down arrow to do this.

I created a second column to the letters you are sorting. Visually this works, but apparently "" in a cell makes it not empty anymore and the ctrl + down arrow no longer works.


Column A, Column B, what displays in Column B
a, =A1, a
a, =IF(A2<>A1,A2,""),
b, =IF(A3<>A2,A3,""), b
b, =IF(A4<>A3,A4,""),
c, =IF(A5<>A4,A5,""), c

Simi

[Ken Puls]

Hi Simi,

I'm not totally following this one. What's the end goal that you're trying to solve? We may be better to craft a new solution than come up with a way to modify this one.