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.

23 comments

Your email address will not be published. Required fields are marked *

  • Hello sir, could you help me with this question: How to write a vba code that enable us to jump n cell (down/ up/ to the right/ to the left) from a certain cell. (n is an integer).
    Thank you very much!

  • 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

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

  • 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

  • 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).

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

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

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

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

  • 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

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

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly