• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 21. Excel Essentials > Troubleshooting


Resetting the Last Cell

Q1:I pressed Ctrl+End to go to the last cell in my worksheet, but I ended up with the insertion point in a blank cell below and to the right of the actual end of the sheet. How do I convince Excel to jump to the actual end of the sheet?
A1: When you select the last cell in a worksheet, either by using the Go To Special dialog box or by pressing Ctrl+End, Excel actually jumps to the last cell that has ever contained data or formatting. As you've seen, that can produce unexpected results, especially if you've deleted a large number of rows or columns (or both) from a list or worksheet model, or if you once placed a range of data in an out-of-the-way location and then moved or deleted it. In that case, selecting the Last Cell option might position the insertion point in a cell that's far beyond the actual end of the sheet. To reset the sheet so that you can truly jump to the last cell, delete all rows that are between the actual end of the sheet and the location that Excel insists on identifying as the last cell, and then repeat the process for all columns that match that definition.

If this is a common occurrence, you can create a one-line macro that will reset the last-cell location in the current sheet. Press Alt+F11 to open the Visual Basic Editor and enter the following code:

Sub ResetRange()
End Sub

Be sure to save the ResetRange macro in an easily accessible location, such as your Personal macro workbook; then run it whenever you encounter a worksheet that needs this type of cleanup.

Adjusting Header and Footer Margins

Q1:I created a complex custom footer for a worksheet, but when I try to print, the footer runs into data at the bottom of the sheet.
A1: By default, Excel positions headers and footers a half-inch from the edge of the page and another half-inch from the worksheet's data. That's ideal for a one-liner, but if you try to add too much information in either place—for example, if you insert a long boilerplate paragraph required by a government agency at the bottom of each sheet—you'll quickly overrun that margin. If you decrease the Top or Bottom margins without also adjusting the Header or Footer margins, your data might also collide. You can enter an exact measurement for any of these margins by using the Margins tab on the Page Setup dialog box. If you've already created the header and footer, however, it's much easier to set the margins visually. Choose File, Print Preview; click the Margins button, if necessary, to display the margin markers along each edge of the preview window, and drag the indicators up or down until the preview looks right.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint