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
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.
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.
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.
Here are the instructions from the image above on how to assign a keyboard shortcut to a macro.
- Select the View Tab in the Ribbon.
- Click the Macros Button.
- Select the file the macro is stored in.
- Select the macro from the list.
- Click the Options… button.
- Hold Shift and type the letter of the shortcut key.
- 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.
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.
How else can we enhance this macro or navigation through the worksheet/workbook? Please leave a comment below with any questions or suggestions.