Shortcut for Selecting a Data Range

@Willy
I used my previous macro only to reset the range as I don't necessarily want to jump to the last cell when I'm resetting the range. For example, when working with filtered lists, I just want to remove blank rows/columns without changing the active cell.

This morning I found an even shorter one liner that does exactly that.

Sub ResetRange()
ActiveSheet.UsedRange
End Sub

Thanks

@Ruthie
I use this one liner which does the same job and jumps to the "really" last cell:

Sub ResetLastCell() 'assigned shortcut: Alt+Ctrl+End
ActiveSheet.UsedRange.SpecialCells(xlLastCell).Select
End Sub

@ Willy Vanhaelen, @ J. Woodly, @ Billy Thomas

If you reset the used range on your worksheet, Ctrl + End shouldn't pick up those "empty" cells. I use the following macro because I run into this a lot with workbooks I get from other people.

Sub ResetLastCell()
Dim x As Long
Dim y As Long
x = ActiveSheet.UsedRange.Rows.Count
y = ActiveSheet.UsedRange.Columns.Count
End Sub

@ J. Woodly, @ Billy Thomas

Crtl+End selects the last cell in a worksheet.

Ctrl.+Shift+End extends the selection from the active cell to that last cell.

The last cell in a worksheet is the cell in the bottommost row and rightmost column of the worksheet that contains data or it can be empty but has been formatted. So this cell can be anywhere in the worksheet below and/or to the right of your data.

If your worksheet is well maintained, the last cell will will be the last cell containing data.

If you entered data in a cells byond that and deleted it (with or without formatting), Excel remembers the old position until you save the workbook.

If you forgot to delete the formatting you still get the old position after saving.

Ctrl+Shift+End apparently selects an extra empty row at the bottom if it has been formatted differently, including different height. And Ctrl+Shift+End apparently selects an extra empty column at the right if it has been formatted differently, but not if width is the only difference.

Ctrl Shift End will also select all data in a contiguous section, including the current row and column and all rows below and to the right.

This is a realy useful tip, and I just tried it in a worksheet that I created yesterday. Users should be aware that Excel treats cells that contain formulas as occupied, even when the formula resolves to a blank.

I see now. The Google cached version shows the same page, so it just looks like the comments got removed. The Way Back Machine version shows that it was, in fact, a shorter article before the edits, so now removing the comments makes more sense. (I did wonder why I bothered to comment about Ctrl+A when it was already in the article). I guess I didn't realize my comments got integrated into articles when they were rewritten.

http://web.archive.org/web/20111110024804/http://excelribbon.tips.net/T008966_Shortcut_for_Selecting_a_Data_Range.html

That is by design, Bryan -- it is not a problem.

This tip, as republished, is different than the older version. It includes many of the substantive comments, reworked into the tip itself. Had the comments been left there, they would have been superfluous, repeating what was now within the body of the tip.

Old comments go away, by design, when tips are republished.

-Allen

Allen, I think there is a problem with your website. Occassionally when you post a premium tip, the comments which were there before are deleted.

----

James Cameron 30 Sep 2013, 21:11
Also, in keeping with Excel's penchant for having multiple ways of acheiving the same end, CTRL+A and CTRL+Shift+Space appear to function identically.

----

Richard Meijles 30 Sep 2013, 15:09
ctrl+Shift+8 puts the active cell directly in the left-upper cell of the selection, whereas ctrl+A leaves the active cel unchanged.

If you define a table as a table, pressing ctrl+a for the first time selects the data of that table. Pressing ctrl+a for a second time also selects the headers. Pressing ctrl+a for a third time selects the whole sheet.

If you define a table as a table and you press ctrl+shift+*, the entire table is seelcted including the headers. Pressing this combination a second or third tie doesn't change anything.

Ctrl+Shift+* will also select a rectangle shape when the active cell is directly next to a cell from a table.

So there are a few differences.

----

Bryan 30 Sep 2013, 10:45
Ctrl+A seems to do the same thing, but it's a lot easier for me to hit. The only difference I can see is if you are not in a data range at all, Ctrl+A will highlight the entire sheet, whereas Ctrl+Shift+* only works within data.

----

Source: http://webcache.googleusercontent.com/search?q=cache:-eJj1BLOsAEJ:excelribbon.tips.net/T008966_Shortcut_for_Selecting_a_Data_Range.html+&cd=2&hl=en&ct=clnk&gl=us