Last Cell of Worksheet

[Joe Kreidler]

The shortcut CTRL+END is very handy for moving to the last
cell on the worksheet, in the bottom-most used row of the
rightmost used column. This shortcut works until I delete
rows at the end of the worksheet. After I delete a few
rows, the CTRL+END shortcut takes me to the last cell that
used to hold data, not the last cell that contains data.

Is there a shortcut that moves to the last cell on the
worksheet that contains data? If not, is there a way to
change what Excel thinks is the last cell so that the
CTRL+END shortcut takes me to the last cell that contains
data?

Thanks in advance,

Joe
(e-mail address removed)

 

[Ken Wright]

Go to the bottom of your real data, and then select all the rows from there to
the bottom (Actually select the row headers so you get the entire rows). Now do
Edit / Delete / Entire Row. Do the same for the columns. Save the file, close
it and then reopen. CTRL+END should now work normally.

 

[Earl Kiosterud]

Yup. And a quick way to select rows to the bottom, after you've selected the
first empty row (below your data) is to press Ctrl-Shift-Down.

 

[David McRitchie]

Hi Joe,
For the other suggestions in manually deleting rows and columns
you will need to save the file.

Using a macro is even easier and you don't need to actually
save the file in order to see the effect. You will need to save for the
change to be permanent, of course.

Sub Fix_all_lastcells()
Dim sh As Worksheet, x As Long
For Each sh In ActiveWorkbook.Worksheets
x = sh.UsedRange.Rows.Count 'see J-Walkenbach tip 73
Next sh
End Sub

For more information including links to how to install a macro see
http://www.mvps.org/dmcritchie/excel/lastcell.htm#resetall