Shortcut to jump to last non-empty row in range skipping empty rows

Question

How can I jump from one cell in a column to the last non-empty cell in that column whereas there are empty cells in between?

Example: Here I want to use a shortcut to jump from Cell 1 to Cell 5. (Ctrl + arrow down is not working, because it will stop at Cell 2.)

Column A
Cell 1 with value
Cell 2 with value
_empty cell_
Cell 3 with value
_empty cell_
Cell 4 with value
Cell 5 with value

Answer 1

As mentioned above in comments, CTRL+END will take you to the last cell in the worksheet.

If you want to go to the last cell in a column, AFAIK, you may need a macro as (which you noticed) CTRL+DOWN will stop at the first empty cell.

Put this macro in a module in your workbook, and you can then assign a keyboard shortcut to it and it'll select the last non-empty cell in the active column.

Sub gotoLastRow()
With ActiveSheet
    .Cells(.Rows.Count, ActiveCell.Column).End(xlUp).Select
End With
End Sub

Answer 2

1. Click the column header label to highlight the column. (Notice the selected cell will be the row 1 cell for the column, just under the column header label.)

2. Press Shift-TAB to "move up a cell" in the column. Like with video games of yore, going off screen at the top will bring you back on screen at the bottom. You now have the last cell in the column (row 65536 or 1048576) selected.

3. Press your choice of methods for moving up the column to the last used cell above the one you have selected. I use End-Up Arrow and sometimes Ctrl-Up Arrow, but there are likely others as well.

4. Since you were in the absolutely last cell in the column, by definition the last cell used above the one you are in is the last used cell in the column.

Done.

No Ctrl-End and then wobbling about to the correct column or other chance-of-error type wandering around. One set of a couple key presses, always in the desired column.

(As to finding such in the (unknown) column that has the lowest used cell, Ctrl-End puts you on its row, then End-Left Arrow or Ctrl-Left Arrow takes you right to the lowest used cell in any column at all.)

To get really wild, and not use the mouse at all, arrow your way into the column, then press Ctrl-Space Bar to select the column. Press Ctrl-. ("Control-Period") to move to the row one cell. Press it again to move to the last row's cell. (If already in row 1, only one pressing is needed.) Then up to the last used cell with End-Up Arrow.

And to go REALLY crazy, highlight the column using Ctrl-Space Bar from whatever cell in the column you are in, avoiding that being row 1's cell. Then press Ctrl-| (or as some would prefer to write that, Ctrl-Shift-|) (it is the "pipe symbol" above the backslash key).

All, and ONLY, the used cells (non-true-blank cells), even ones that result in "", will be selected and the row 1 cell will be the working cell. Pressing Shift-TAB will now "roll around" to the LAST of those used cells. Directly to it.

(I was particular to say not row 1's cell because the key combo works slightly differently then. Noticeably differently! Not like it causes a tsunami, but it does act differently enough it is not helpful in this task.)

Answer 3

1:

Your quickest way, though it's still two steps, would be to use the Name Box or Go To to jump to the last cell in the column you're interested in, then use Ctrl+Up.

Assuming we wanted to find the last used cell in column A, you could click in the Name Box and enter A65536 (if you're in compatibility mode) or A1048576 (if you're not), then hit Enter. That will jump you to the end of the sheet. You can then use Ctrl+Up as normal to find the last actually used cell.

If you know roughly how long your data is, though, you don't need to use row 65536 or row 1048576 - any row that you KNOW is beyond the end of your data would work.

2:

It may be simpler, though it's still an extra couple of key presses, to use the Ctrl+End method suggested in other answers. The downside to this is that you need to navigate back to the column you actually want, which of course may or may not extend all the way down to the end of your data.

Answer 4

Keyboard Shortcut

Control+End then Control+← then Control+↑

Fastest Way

Hold down the Control key continuously during the sequence, releasing it only at the end.

Control+End+←+↑