XL2000: Excel Considers Cell Outside Data Area as Last Cell in the Worksheet

• Microsoft Excel 2000

SYMPTOMS

Excel may calculate the last cell in a worksheet to be in a row below any data or in a column to the right of any data. Depending on the size of the unused area, this may cause the worksheet to have a larger file size or perform slowly.

CAUSE

There are a number of things that Excel looks at to determine the used range of a worksheet. The "last cell" is usually defined as the intersection of the furthest column to the right that contains data, and the lowest row that contains data. The "used range" is the rectangle of cells with the upper-left corner in cell A1, and the lower-right corner in the "last cell."

However, some other conditions may cause Excel to consider a cell as being used, thereby increasing the size of the used range beyond those cells that contain actual data or formulas. For example, any change to a cell that makes it different from the Normal style marks the cell as used, including changes to:

• Number formats
• Text alignment
• Fonts
• Font formats such as color, font style (bold), size
• Borders
• Patterns
• Cell protection such as locked or hidden

• Changed row heights (but not column widths)

RESOLUTION

It is often useful to make a worksheet smaller, in order to save file space, increase calculation speed, and reduce the amount of time Excel needs to open the file. However, you can only clear or remove rows from the bottom of, or columns from the right of the area that Excel considers to be used. The used range of an Excel worksheet always begins at cell A1, even if the first row or column is totally empty of data and contains no format changes.

To change the used range in Excel, follow these steps:

1. Clear or delete the cells that define the range boundaries, or change the formatting back to that of the default Normal style. To do this, use any of the following methods:



• Select the area on the right or bottom that you want to remove from the used range, and press DELETE or point to Clear on the Edit menu, and click All.
  
  
• If you want to remove entire rows or columns, click the row numbers or column letters, and then click Delete on the Edit menu.
  
  
• If you need to change row height back to the default, select the appropriate rows, point to Row on the Format menu, and click AutoFit.
  
  
2. Save the workbook.
   
   After you clear an area, Excel does not recalculate a smaller used range, until you save the workbook.

MORE INFORMATION

To find the "last cell" or end of the used range of a worksheet, use either of the following methods:

• Press CTRL+END.
  
  -or-
  
  
• On the Edit menu, click Go To. Then, click Special. In the Go To Special dialog box, click Last cell, and then click OK.

1. On the Format menu, click Style.



2. In the Style dialog box, click Normal in the Style name list, and click Modify.



3. Click each of the tabs and make changes as appropriate. Then, click OK.

REFERENCES

For additional information about the behavior of the last cell in Microsoft Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

Q231007 XL2000: Deleting a Cell Changes the UsedRange Property

Additional query words: XL2000

Keywords : xlformat
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb