Results 1 to 6 of 6

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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,

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,276
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,276
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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:


    Code:
    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,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,276
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •