Software

Office challenge: What's the quickest way to select the last cell to the right or bottom of a data range?

In this week's Office challenge, learn how to hide an Access query generated by code and test your skills at sheet navigation with a new Excel challenge.

Let's suppose a user works with a large dataset - too large to fit on one screen. In fact, it fills many screens. To get to the outer areas of the data range, this user scrolls or uses [Ctrl]+[RightArrow] and [Ctrl]+[DownArrow]. When you show her a quick trick for moving to the last cell to the right or to the bottom of the data range, she's thrilled! What did you show her?

Last week we asked… How would you hide an Access query? Hiding a fixed query is easy enough; hiding a query generated by code is another matter. First, I'd want to know why - is it even necessary? Quizmaster reminded us that adding the uSys prefix to an object automatically hides it. Amasa gave us an interesting reason for hiding the query. Thanks to Doug for his great list of reasons and his generous list of hiding methods. Now, here's what the reader ended up using:
object.SetHiddenAttribute(objecttype, objectname, hidden)

where objecttype equals acQuery and hidden equals True (setting the attribute to True).

Thanks for another great challenge!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

13 comments
Parazanda
Parazanda

Also ... if the CTRL + RIGHT or DOWN ARROW will take you to the last point of data in a range ... then holding down the SHIFT key at the same time will select the data range ... Any keystrokes (or mouse+key combo's) used with the SHIFT key will select ... Another way to select a large range of cells is to use the NAME box. Select the first cell in the range, type the last cell you want selected into the NAME box and then press SHIFT+ENTER.

david.hanshumaker
david.hanshumaker

The question was how to quickly select the last cell in a row OR column of data. Not the bottom right cell. For example, I can understand that a person may want to get to the last cell in column A, in order to move down one row, and enter a new record. I believe we must assume the data can contain blank cells anywhere, otherwise, CTRL {down or right arrow} does the trick. I don't a slick way to handle blank cells, but it seems every response so far has missed the question. Further clarification may also be needed. Is it desired to select the last cell in a particular column in a range of data (may be blank), or the last cell with an entry? Same for a row. Am I nit picking?

gkam21
gkam21

To get to the last data cell use Ctrl+Shift+END. If you are in cell A1 and use this key combo the curser will go to the last "bottom right" cell and highlight all the data. This will work no matter where you start

cyndi
cyndi

Then select Special in the dialog box and then Last Cell.

williams
williams

Ctrl End gets you to the last cell in the working area of the spreadsheet. This may or may not be the bottom right hand cell in the range. I agree with the earlier post that says end right arrow then end down arrow

steveh
steveh

To get to the end of a filled row END then Right Arrow. To get to the last cell in a filled column END then Down Arrow. NOTE: Both of these this will take you to the last filled cell. However, if it encounters an empty cell it will stop at the preceding cell, so all cells must be filled within a row or column.

oldbaritone
oldbaritone

Goto it will have a list of the named ranges

Spitfire_Sysop
Spitfire_Sysop

This will take you to the far right and the bottom just like CTRL+HOME will take you to A1 except CTRL+END will only take you as far as the data goes. The last cell down and the last one to the right which is sometimes an empty cell.

williams
williams

Isn't that just the same as Ctrl End?

dhays
dhays

in this sheet, I am trying the suggestions. It appears Ctl-End takes me to AS 5442 and the last cell with data is nowhere near T423, where the last cell with data is. So no ctl-end doesn't work for me. (Excel 2003) In a different sheet it was closer to where it should be it took me to AA 458 last cell with data is M451.

glennho
glennho

Yes, and it takes more keystrokes - Ctrl G Alt S S Enter

mwb78
mwb78

If the keyboard shortcut takes you to a row or column beyond your last cell with data, it could be because it has taken you to the last previously-used cell. Even if the contents have been deleted and the cell appears empty, to Excel the cell appears to have been used. To avoid the confusion, I try to remember to delete newly emptied cells by deleting rows and columns where appropriate. This keeps my last used cell more in sync with my last cell that contains actual content.

Editor's Picks