25

Excel Shortcuts to Move the Selected Range with the Arrow Keys

Bottom line: Learn how to create custom shortcuts to move the selection to an adjacent range.  This technique is great for selecting rows or columns that contain blank cells.

Skill level: Intermediate

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

 Select Adjacent Rows or Columns

Have you ever wanted to quickly select a row or column next to the current selection?  The following screencast shows an example.

Select Adjacent Ranges with Shortcuts

There are other ways to do this natively in Excel using a combination of shortcuts.  For example, we can move the selection one column to the right using the following shortcut sequence: Shift+Right Arrow, Tab, Shift+Right Arrow

That only works if one row or column is selected, and is a lot of keystrokes.  So, in this post I share a way to accomplish this same task with a single keyboard shortcut or button press.

This is very useful when you want to select an adjacent row or column that contains blank cells.  In a previous post & video I explained 2 ways to select rows/columns with blanks.  That included using an Excel Table or a macro.

The Move Selection Macros

This technique uses a set of four very simple macros.

The Range.Offset property allows us to reference the range to above, below, left, or right of the current selected range (Selection).  We can use the RowOffset and ColumnOffset parameters to tell Offset how many rows or columns to move.

Selection.Offset([RowOffset], [ColumnOffset])

MSDN help page for Range.Offset

In this case we only want to move one row or column, so we will set the RowOffset or ColumnOffset parameter values accordingly.

Download the Excel File

You can download the file that contains the macros here.

Move Selection Macros.xlsm (22.4 KB)

Video: How to Add Code to Your Personal Macro Workbook

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

The Macro Code

Here are the macros that you can copy/paste to your Personal Macro Workbook.  Checkout my video series on How to Create a Personal Macro Workbook.

Sub Move_Selection_Right_Column()
'Moves selection one column to the right

  On Error Resume Next
    Selection.Offset(0, 1).Select
  On Error GoTo 0
  
End Sub

Sub Move_Selection_Left_Column()
'Moves selection one column to the left

  On Error Resume Next
    Selection.Offset(0, -1).Select
  On Error GoTo 0

End Sub

Sub Move_Selection_Down_Row()
'Moves selection down one row

  On Error Resume Next
    Selection.Offset(1, 0).Select
  On Error GoTo 0
  
End Sub

Sub Move_Selection_Up_Row()
'Moves selection up one row

  On Error Resume Next
    Selection.Offset(-1, 0).Select
  On Error GoTo 0
  
End Sub

On Error Resume Next is used to bypass any errors that are raised when the first/last row/column in the sheet is selected, and the user presses the shortcut to select a row outside the sheet.

How to Run the Macros

There are a few options for calling/running the macros.

Custom Ribbon Button

We can assign the macros to custom macros buttons on the Ribbon. Checkout the 3rd video in the series on the Personal Macro Workbook for step-by-step instructions on how to create the buttons.

Custom Ribbon Button to Call Macros

Keyboard Shortcuts

We can also assign keyboard shortcuts to the macros.  Typically the macro shortcuts start with Ctrl or Ctrl+Shift, and can be assigned in the Macro Options window.  However, we can also create shortcuts to start with a different key by using the Application.OnKey method.

The following macro creates the keyboard shortcuts that start with the Alt key.  We use a combination of Alt, Shift, and directional arrow keys to call the shortcuts.

This macro needs to be run once every time you open Excel.  It can be put in the Workbook_Open event in the Thisworkbook module of your Personal Macro Workbook.  I explain more in the video above.

Sub Add_Move_Selection_Keyboard_Shortcuts()
'Create keyboard shortcuts to call Move Selection macros
  '% - Alt
  '+ - Shift
  'Help for OnKey Method: https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/application-onkey-method-excel

  'Set Alt+Right Arrow to call the macro
  Application.OnKey "%{RIGHT}", "Move_Selection_Right_Column"
  
  'Set Alt+Left Arrow to call the macro
  Application.OnKey "%{LEFT}", "Move_Selection_Left_Column"
  
  'Set Alt+Shift+Down Arrow to call the macro
  Application.OnKey "+%{Down}", "Move_Selection_Down_Row"
  
  'Set Alt+Shift+Up Arrow to call the macro
  Application.OnKey "+%{Up}", "Move_Selection_Up_Row"

End Sub

Worksheets Shapes or Buttons

Another option is to put shapes or buttons on a worksheet that call the macros. This would only be used for specific applications where you wanted to make it easy for the user to select an entire row or column.

Call Move Selection Macros with Arrow Shapes

What’s Your Favorite Technique for Moving the Selection?

I want to thank Ed, a member of the Excel Campus community, for inspiring this idea. It’s a very simple solution to a common problem of selecting rows or columns that contain blank cells.

It’s also an easy way to move or jump the selection to apply or copy/paste formatting.

What other methods do you use to move the selection.  Please leave a comment below. Thank you! 🙂

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 25 comments
Tekena - May 2, 2018

Hi Jon! I learnt a lot from this thanks. I’m trying to create a VBA macro that can add multiple sheets using feedback from a user inputbox. I’ve create the macro but the sheets are adding in reverse order. For example instead of “Sheet1 then Sheet2, Sheet3, Sheet4”, it is “sheet1 then Sheet4, Sheet3, Sheet2”. What can i do to correct this? Here’s the code.

Sub Add_Sheets()

‘ Add_Sheets Macro
‘ Creates multiple sheets as per user input

Dim myDecision As VbMsgBoxResult
Dim myCounter As Long

myDecision = MsgBox(“Are you sure you want to run this macro?”, vbOKCancel + vbExclamation, “Run Macro”)

If myDecision = vbOK Then
myCounter = InputBox(“How many sheets would you like to add?”, “Input”)
Sheets.Add after:=ActiveSheet, Count:=myCounter

Else
Exit Sub
End If
End Sub

Reply
    Jack - May 8, 2018

    Hi Tekena,

    You have “Sheets.Add after:=ActiveSheet” so the active sheet to start is Sheet1. On the first pass, the macro will create Sheet 2 right after (next to) Sheet 1. But then it will create another new sheet (Sheet 3) right after Sheet 1, so now Sheet 2 is “pushed down the line” so you get Sheet 1, Sheet 3, Sheet 2. Then when it creates Sheet4, the same thing happens, you get: Sheet 1, Sheet 4, Sheet 3, Sheet 2. The reason for this is that Sheet 1 is always your active sheet in your code. Maybe what you could try is making the added sheet the active sheet, so it would work like this: Sheet 1, then Excel adds Sheet 2 and makes Sheet 2 the Active sheet. So when it goes to add a new sheet “after the active sheet” it will give you Sheet 3 after Sheet 2, so it will be: Sheet 1, Sheet 2, Sheet 3 and so on. So one strategy you could use is adding a sheet after the active sheet, but then immediately make the added sheet the active sheet, that way when Excel goes to add a new sheet after the active sheet, it will go in order, 1 2 3.

    Reply
Sugus - April 30, 2018

Thanks so much john, it very nice and needs to learn more your teaching.

Reply
Matthias - April 29, 2018

Thanks Jon, I appreciate the preview you gave for the OnKey explanation!

Next time I need to move selections, I hope to remember the paste buddy’s paste special.

Mostly these selections do not have comments or have the same formatting. So the shortcuts for copying comments or formatting could be used to mark the same range in any other column.

Also Column Width can be used for columns with the same or comparable width.

For rows the Column Width copying would work perfectly – no restrictions whatsoever.

I hope to have soon a chance to use this. 🙂

Reply
    Jon Acampora - April 30, 2018

    Sounds great Matthias! Yes, you could definitely combine this with other Paste Special shortcuts that you create with The Paste Buddy Add-in or the Alt+E+S+… shortcuts.

    Reply
Paul Mason - April 27, 2018

Clever little routines. Thanks Jon

Reply
JK - April 27, 2018

Hey ! Jon
This is really awesome!
Really appreciated with your sharing. It saves my workload a lot.

Reply
    Jon Acampora - April 30, 2018

    Happy to hear it. Thanks JK! 🙂

    Reply
Steve - April 27, 2018

Hi Jon,
Thanks for the great idea, just one question, in the Sub Workbook_Open() routine the one line of code is:

Call m_SelectRange.Add_Move_Selection_Keyboard_Shortcuts

Can you explain what is going on with the ‘m_SelectRange’, I must be missing something…

Reply
    Jon Acampora - April 27, 2018

    Hi Steve,
    I just added another video in the post above that explains how to add the macros to your Personal Macro Workbook, and the Workbook_Open event.

    m_SelectRange is the name of the code module that the “Add_Move_Selection_Keyboard_Shortcuts” is stored in. You do NOT have to add this reference to the line of code that calls the macro. In fact, the line of code could be as simple as just the macro name. You don’t even have to use the Call statement.

    However, I like to use the Call statement in my macros to make them easier to read. It clearly tells us that the line is calling another macro. As you’ll see in the video, when typing the line of code, using the module name makes it easy to then select the macro name from the intellisense drop-down after I type a dot. This means I don’t have to navigate back to the other code module to copy the macro name.

    So, those are just a few shortcuts and tips I use to help make coding a little easier. Again, not completely necessary. I hope that helps. Thanks again and have a nice weekend! 🙂

    Reply
Manuel - April 27, 2018

Amazing Video

Reply
Jeff Carnohan - April 27, 2018

This is brilliant Jon! Thank you!!

Reply
Sabir Guiri - April 27, 2018

Hi Jon

Hope you are well.

Do you have an spreadsheet file as a test?

Regards
Sab

Reply
    Jon Acampora - April 27, 2018

    Hey Sab,
    Yes, I just added the file to the page above in the Downloads section. It might take a few minutes for it to update and display on the page. Thanks for asking! 🙂

    Reply
      Sabir Guiri - April 30, 2018

      Great work, Jon.

      Thank you for sharing with us.

      Reply
Mickael - April 27, 2018

Jon,

You’re the man! Thank you so much for sharing your knowledge.

Mickael

Reply
Graham - April 27, 2018

I use the shortcut of [CTRL + .] to move around the corners of a selected area which then allows you to change your selected area as required easily with the SHIFT and ARROW keys.

Reply
    Jon Acampora - April 27, 2018

    Thanks Graham! Yes, that is another good technique! You can also use the Tab key if the selection is just two columns. Ctrl+. works great when the selection is more than two columns.

    Reply
Edil - April 27, 2018

GR8 Jon. I like this, I learned again and it’s very useful for me! I used the right arrow and then Ctrl+Shft+Down to select ranges, but now I can do it much faster thanks to you!

Reply
Dan Franceski - April 27, 2018

Jon –

Wonderful set of macros, thanks.
One minor error- you have this comment line in every macro:

‘Move selected cells one column to the right

Of course there should be a different comment for “column to the left”, “row up” and “row down.

Dan

Reply
    Jon Acampora - April 27, 2018

    Thank you for letting me know Dan! Appreciate your help. I fixed the comments. 🙂

    Reply

Leave a Reply: