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.

Editor's Picks