Software

Reset the last cell of your Excel worksheet

Excel often will default to the last cell you've entered data into—even if you've wiped it out. Mary Ann Richardson shows how to create a macro that will reset the unwanted cell.

Your job involves adding data to the end of a very large Excel worksheet. To get to the last cell to begin data entry, you press [Ctrl][End], but Excel will take you to a blank cell below and right of the actual data. You have to scroll back to get to the last cell containing data, which is something you tried to avoid by using the shortcut keys.

Before concluding that the feature does not work, remember that Excel jumps to the last cell that has ever contained data or formatting—even a cell where data or formatting was deleted. You could delete all the rows between the actual last cell of your spreadsheet and the cell that Excel says it is, or you can create a macro that will reset the last cell for you. Follow these steps:

  1. Open a worksheet in Excel and press [Alt][F8].
  2. Enter ResetRange in the Name box and click the Create button.
  3. Enter the following code at the prompt:
       ActiveSheet.UsedRange
  4. Press [Alt]Q.

It is a good idea to place this macro in a central folder, or attach it to a tool in a toolbar, so you can use it to reset the last cell for any worksheet.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

13 comments
BRADFERNANDES
BRADFERNANDES

FORGET ALL THIS.... FIGURED IT OUT!


1) GO TO LAST CELL WHERE YOU WANT TO SAVE.

2) GO TO VIEW ~ PAGE BREAK PREVIEW

3) HOLD CONTROL & USE YOUR MOUSE TO SCROLL TO SIZE YOU WANT.

4) SAVE


DONE!

BRADFERNANDES
BRADFERNANDES

FORGET ALL THIS.... FIGURED IT OUT!


1) GO TO LAST CELL WHERE YOU WANT TO SAVE.

2) GO TO VIEW ~ PAGE BREAK PREVIEW

3) HOLD CONTROL & USE YOUR MOUSE TO SCROLL TO SIZE YOU WANT.

4) SAVE


DONE!

acandiani
acandiani

Thank you!! It has been years wondering how to make this happen.

nomodrama
nomodrama

In MS Excel 2003, I am unable to get the macro to work and simply saving my file does not reset the last used cell. However, I did experiment with all the suggestions and was able to get the following to work: ctrl + end, highlight all rows that "appear" empty, right mouse click, delete (yes the rows are highlighted and the last row appears to be the same, ctrl + home, save. This did reset my last used cell. The macro probably works, I just have to figure out which bell or whistle I have turned on or off currently. Thanks, I work for the state and my Excel files are huge. This allows me a little more space.

ephemia
ephemia

Use the Ctrl and down arrow key. This method, used repeatedly, also allows you to 'jump' between non-contiguous blocks of data. Experiment and use the other arrow keys. Keep pressing the key combination until you reach the last row/col and return using the 'opposite' direction key.

grahamrice
grahamrice

The macro as written does not work, for Excel 2000 (I believe 95 and 97 were similar) and I assume that it has not changed for later editions. The macro should be something like :- ActiveSheet.UsedRange.SpecialCells(xlLastCell).Activate Also the other option suggested, deleting rows or columns between the actual last cell of your data and the last cell as determined by Excel, does not work. The position of the ?last? cell as determined by Excel remains unchanged. However although I am a fan of macros, this particular macro is not necessary !! Excel does this task automatically everytime a Workbook is saved. It does NOT need to be closed, you can simply save an ?open? Workbook. Additionally saving a Workbook completes this task for EVERY Worksheet within a Workbook, not just the currently ?active? worksheet. If you used the macro option, then you would need run it separately on each Worksheet ? or create a more complex macro.

RickyF
RickyF

I think the headline is in error.

jlcopp
jlcopp

I, too, have Excel 2003 and I thought I was doing something wrong. I kept trying to make the macro work to no avail. Your alternative works like a charm. With some of the large spreadsheets I work with, this is certainly a time-saver.

juliang
juliang

I also have been looking for this answer for years. The macro as described in the first instance, works fine. I will try this other command later. What I found Not to be true is that Excel determines that last cell after it saves the file. At least for me, saving the file AFTER deleting empty rows and columns, does not reset the last cell. When I use either CTRL-END or GOTO last cell, it brings me to the same empty cell several rows and columns from where the last active cell is. I have used every version of Excel since 97. (i'm now using 2007)

kees lucassen
kees lucassen

First I have to say that the macro does work with my version of Excell 2000. Excell does the trick when a workbook is saved and opened again. But the macro helps me when I use it on large workbooks where I've deleted several rows or columns at the end and I frequently have to jump to the end with [End][Home]. Until now I had to close the workbook and open it again.

chobbs
chobbs

I have been looking for this solution literally for YEARS. I have already shared it with several coworkers. Thank you Mary Ann!!!

Leee
Leee

It's fixed now.