Move to the bottom or end of a selected range in Excel

Hello,

Is there a shortcut (hotkey) or other way to move the active cell to the bottom or end of a selected range in Excel?

For example, suppose you click cell B3 and then — while holding down the {Shift} key — press the {Down} arrow six times and the {Right} arrow twice, to select the range B3:D9 as shown in the following screenshot:

range.PNGOnce that has occurred, Excel continues to view the active cell as B3 — which is evident by pressing any directional keys (eg {Right}, {Up}, etc) and noticing that the ensuing movement originates from B3.

I'm wondering if there is a way — once a range has been selected — to move the active cell to the bottom of the range (in this case that would be cell B9) or to the end of the range (cell D9 in the example). By the way, it doesn't matter whether the selected range remains selected with the described movement or not.

Obviously, this can be accomplished with a mouse click or (if the entire selected range can be seen) by manually use of the directional keys. But I'm looking for a shortcut which will move to the bottom or end of a selected range without using the mouse and when the range is too large to see all of its borders.

Thanks
WeThotUWasAToadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Add this macro to a module.

Sub SelectLast()

Cells(Selection.Rows.Count + Selection.Row - 1, 1).EntireRow.Select

End Sub

Open in new window



My apologies if you already know how to add a macro but just in case...

In Excel, Press Alt+F11 to open Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu

Copy the macro (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

Optionally, press Alt+F8 to open the "Macro" dialog window. Select the macro, click ‘Options…’,  hold down the Shift key and type the letter L (or any other letter) and click ‘OK’.  Then anytime you want to run the macro press Ctrl+Shift+L.
0
WeThotUWasAToadAuthor Commented:
Martin Liss
My apologies if you already know how to add a macro but just in case...
A comment even before trying the macro:
Martin, absolutely no need to apologize because I am always confused (when I receive a VBA solution) about how to actually insert it into the VBE. Therefore, your specific instructions are very helpful and very appreciated.
0
WeThotUWasAToadAuthor Commented:
Martin,

Your solution does not do exactly what I had in mind (since it selects the entire spreadsheet row corresponding to the bottom row of the selected range). However, it will get me where I'm headed in my current project so thank you very much.

Out of curiosity, in the same way your VBA code identifies the bottom row, can additional code be included to identify the furthest right column? If so, then could those two results be combined so as to activate the furthest bottom-right single cell (ie cell D9 in the screenshot above) rather than a full row or full column?

Thanks again
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Martin LissOlder than dirtCommented:
Change the macro to this.

Sub SelectLast()

Dim lngLastRow As Long
Dim lngFirstCol As Long
Dim lngLastCol As Long

lngLastRow = Selection.Rows.Count + Selection.Row - 1
lngFirstCol = Selection.Column
lngLastCol = Selection.Columns.Count + Selection.Column - 1

Range(Cells(lngLastRow, lngFirstCol), Cells(lngLastRow, lngLastCol)).Select

End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
BTW the above will begin the highlighting at the first column selected so if you chose B115:D125 it would highlight B125:D125 and not A125:D125. If you want the latter let me know.
0
Rory ArchibaldCommented:
Ctrl+. will move the active cell clockwise to each corner of the selection in turn. So you'd need to press it twice to get to D9 in your example, or 3 times for B9.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeThotUWasAToadAuthor Commented:
Thanks Rory. That is exactly what I was hoping to find.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.