Keyboard Shortcut to Jump to Any Row or Column - Excel Campus
22

Keyboard Shortcut to Jump to Any Row or Column

Bottom line: In this post I explain how to create a custom keyboard shortcut to jump to any row or column.  This solution uses a simple macro, and is much faster than the built-in methods in Excel.

Skill level: Intermediate

Keyboard Shortcut to Jump To Any Row or Column in Excel

Have you ever been on the phone with someone while looking at a spreadsheet, and they say something like, “Go to row 225.”?

Or, “Go to column AC in the same row you are currently in.”

This type of navigation usually requires you to do a lot of scrolling.  You might also use the Name Box or Go To menu in Excel to make these jumps.

However, both the Name Box and Go To menu require you to input the full cell address you want navigate to.

Name Box and Go To Menu Require the Full Cell Address 2

 

It would be nice if we could just type the column letter OR row number in either of these menus, instead of the full address.  Unfortunately, that’s not possible in Excel.

A Simple Macro to Jump to a Row or Column

So, I wrote up this very simple macro that makes it possible to jump to (select) a specific row or column.

Jump to Any Row or Column Macro in Excel GIF
Click here to view the screencast animation in your browser

The macro uses an InputBox to prompt you to enter a column letter OR row number.  You just have to type a column letter OR row number, then hit enter.

  • If you type a column letter, then the cell of the active row for that column is selected.
  • If you type a row number, then the cell of the active column for that row is selected.

So if the current selection (active cell) is D9, and you type 225 in the inputbox, cell D225 will be selected.  If the current cell is D225 and you type ac in the input box, cell AC225 will be selected.

This means you can quickly jump vertically (staying in the same column as the currently selected cell) by inputting a row number.  Or you can quickly jump horizontally (staying in the same row) by inputting a column letter.

You can copy & paste the macro code below to your Personal Macro Workbook.

Sub JumpTo()
'Description: Select the specified row or column
  'If row is specified, active column is used
  'If column is specified, active row is used
'Source: https://www.excelcampus.com/vba/jump-to-row-column-keyboard-shortcut

Dim sResult As String

  On Error Resume Next 'Blanket error handling
  
  'Display inputbox to prompt user for row/column
  sResult = InputBox("Type a row number or column letter and press Enter.", "Jump To...")
  
  If IsNumeric(sResult) Then 'Select row
    Cells(sResult, ActiveCell.Column).Select
  Else 'Select column
    Cells(ActiveCell.Row, sResult).Select
  End If

End Sub

How Does the Macro Work?

The macro uses the InputBox Function to prompt the user for the column letter or row number they want to jump to.  The InputBox function is built into VBA, and is a great alternative to creating a userform for a simple data input like this.

When the macro runs, the InputBox appears.  The user then types the column letter or row number in the box, and presses Enter.

The InputBox returns the input value to a variable (sResult) that temporarily stores the value.

The If Statement determines if the input value is a letter or number.  The IsNumeric function tests for a numeric value and return a TRUE/FALSE.

  • If the input value is a number, then the macro will select the row number of the column of the active cell.
  • If the input value is a letter, then the macro will select the column of the row of the active cell.

The ActiveCell.Row and ActiveCell.Column properties return the row or column number of the cell that is currently selected.

Finally, the Cells(Row, Column).Select method is used to select the destination cell.

Error Handling Note: I put On Error Resume Next at the top of the macro to catch and bypass any errors.  If the user enters a symbol or combination of numbers and letters, then the Select method lines will hit an error.  The error will be handled by Resume Next and no error messages will appear.  Additional error handling could be added here, but I’m not too worried about errors for my own use.  If I mistype in the input box, it’s easier to just try again, then have to deal with an error message.

Assign a Keyboard Shortcut to the Macro

I put this macro in my Personal Macro Workbook, and assign a keyboard shortcut to it.   You can copy & paste the code above and put it in your Personal Macro Workbook or put it in an add-in file.

I use Ctrl+Shift+G as the keyboard shortcut to run the macro.  Since Ctrl+G is the macro to open the Go To window, it is easy for me to remember Ctrl+Shift+G to run the macro to jump to a row or column.

You can use any keyboard shortcut you want though.  It does not have to be Ctrl+Shift+G for this to work.

How to Assign a Keyboard Shortcut to a Macro View Tab

Here are the instructions from the image above on how to assign a keyboard shortcut to a macro.

  1. Select the View Tab in the Ribbon.
  2. Click the Macros Button.
  3. Select the file the macro is stored in.
  4. Select the macro from the list.
  5. Click the Options… button.
  6. Hold Shift and type the letter of the shortcut key.
  7. Press OK.

Now every time you press your keyboard shortcut, the macro will run and the InputBox will appear.

Checkout my article and video series on how to create your personal macro workbook for more info on how to implement this macro.

If you are using my Paste Buddy or Formatting Shortcuts Add-ins, then you might be using this shortcut key for something else.  So check that they do not interfere with any other macros or add-ins you have running.

We Don’t Lose the Undo History

One nice part about this macro is that you do NOT lose the undo history when you run it.  The macro is just using the Select method.  Since the Select method does not make any changes to the workbook that are stored in the undo stack, the undo history is NOT cleared when you run this macro.

Undo History Remains After Running the JumpTo Macro

In my previous post I wrote about why to avoid overusing the Select method in your macros.  But as we can see from this macro, there are definitely times when we can use Select to our advantage.  I guess I felt bad and needed to give Select some love… 😉

Other Ways to Jump?

This macro saves me a ton of time!  If you are ever sharing your screen in an online meeting, your coworkers will be saying, “hey, how did you do that???”, after they see you use this. 🙂

My Tab Hound Add-in take this same principal to the worksheet level.  Tab Hound allows you to quickly type a search for a sheet name, and jump right to it.

Tab Hound Window v1 - Search Feature

How else can we enhance this macro or navigation through the worksheet/workbook?  Please leave a comment below with any questions or suggestions.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 22 comments
Wayne - November 12, 2017

On a BOQ I have many diffrent areas I claim on diffrent sheets how do i stay on one nr when moving between sheets?

Reply
Jen - November 3, 2017

I have a huge spreadsheet with a few data headers that I would like to jump to for easy access – is there a Macro that I could use that would give me the data headers as a choice and then jump to the one I choose? TY in adavance

Reply
Nigel Holder - October 11, 2017

I have a large spreadsheet with every day in the year in the top row.
How can I jump to today’s date quickly using a keyboard shortcut? Thanks!

Reply
Don Anderson - July 11, 2017

Jon,

Great piece of vba code! But of course I would like to modify it a little 🙂
How can I avoid the input box and just use the results of cell A2 in Sheet1 to ‘JumpTo’ a row in Sheet1 Col A2:A400? Cell A2 in Sheet1 contains a list of names that are from Sheet2 A1:A39 (dynamic list). And the rows of data that I want to match A2 to are in Sheet1 A6:A400.

Thanks,
Don

Reply
Pandu - June 12, 2017

Hi!!!Sir,

Thanks for little but very nice macro.

Pls send EXCEL Total shortcut keys to easy working purpose,

Reply
PRAVIN - April 14, 2017

Sir, Really nice code you set that save my time……Perfect!

Reply
Mike - February 13, 2017

Brilliantly simple! Thanks!

Reply
Jaga - January 18, 2017

Thanks bro! Saved alot of time. Great service to ppl in a way! Appreciate it very much! Cheers!

Reply
lokesh kv - July 5, 2016

hello all,

i was stuck in vba error from last 3 days,,.hoping will get help from your blog.
we enabled ssl for database. how to enable or which parameter should be used to enable sslmode in vba data connection(postgresql).

Reply
    Jon Acampora - July 5, 2016

    Hi Lokesh,
    Sorry, I’m not familiar with ssl connections in VBA. You might want to post that in a forum. Thanks!

    Reply
Dave Mason - July 1, 2016

This is close to something I’m looking for, but not quite. Is there a way to have VBA code for when someone enters data into a cell and presses the “Enter” key for activating a different cell other than down one?

I have a macro that creates reports, but some data still needs to be entered manually. If I could enter data in a cell then it jumps to the next cell I need to enter data into rather than entering data, then moving the mouse to select the right cell and at times selecting the wrong cell and typing over data. It gets frustrating. If I can hit Enter and it jumps from C7 to D13 automatically, that would be awesome!!!!

Reply
    Jon Acampora - July 5, 2016

    Hi Dave,
    Yes, it’s definitely possible. We would just need to determine the logic for finding the next cell. Is it the next non-blank cell, or is there some other criteria? Or do you just want to create a list of cells that the macro should jump to.

    You can use the Worksheet_Change event to trigger the macro when the user presses Enter. You can use the Target range to determine if edited cell is one that you want to jump from, and then use logic or a list to determine the destination cell to select.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$C$7" Then
            Range("D13").Select
        End If
    End Sub

    Here is a simple example of what you are describing. Although it will probably get more complex as you add logic into it. The code will be placed in the Sheet module for the sheet you want it to run on.

    I hope that helps. Thanks!

    Reply
Dimitris - June 23, 2016

Awesome..
Thank you so so so much.. You save me a lot of time!!

Reply
    Jon Acampora - June 23, 2016

    Awesome! I’m happy to hear it Dimitris. It’s just a slight variation from the built-in Go To menu, but it’s amazing how much easier it is to not have to type the full cell address.

    Reply
    Sedang Belajar Excel - July 4, 2016

    Many Thanks for this great materials. I Have used excel more than ten years too. But not expert as you. I have to learn more now.

    Reply
REC - June 22, 2016

Thanks for little but very nice macro.

Reply
sq - June 22, 2016

In the TOC you could have a list of all the tabs as Hyperlinks.
The other thought is to have another window (or Button) with Range names that would allow you to go to the named Range.

Maybe add another button for PRINTING. Print the different Tabs or multiple etc

Reply
    Jon Acampora - June 23, 2016

    Thanks for the suggestions sq! I have been thinking about adding range names and Table names to Tab Hound. Good idea! 🙂

    Reply
Alan - June 22, 2016

A good little macro but surely hitting F5 and entering the cell reference does the same thing?

Reply
    Jon Acampora - June 22, 2016

    Hi Alan,
    With the Go To menu (Ctrl+G or F5) you have to enter the full cell reference with BOTH the column letter and row number. If you just type the row number and hit enter, you will get an error message saying the reference is not valid.

    This macro allows you to just type the column letter OR row number. You do not have to type both. I hope that helps. Thanks!

    Reply

Leave a Reply: