Excel: Resetting Last Cell Address Fixes Memory/Printing Probs

ID: Q100406

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0

SUMMARY

In Microsoft Excel, you can press CTRL+END to select the last cell in a cell table or dependency table. In some cases this cell may be well beyond the actual data in your worksheet. In some cases, this cell may result in memory or printing problems.

To reset the last cell address, you must clear all information (data and formatting) from the unused columns and rows of your sheet.

MORE INFORMATION

If you are working with a large sheet that contains many formulas or a lot of formatting, you may experience similar memory problems when you attempt to copy and paste information.

Workarounds

To work around this problem, do any of the following:

Reset the last cell by deleting excess rows and columns

Select all rows and or columns that do not include any data by selecting the appropriate row and/or column headings.

From the Edit menu, choose Clear and select the All option.

From the Edit menu, choose Delete.

Repeat steps 1-3 for as many columns or rows as is necessary.

From the File menu, choose Save As and save the file with another name.

Close the file and reopen it.

Use the following macro to clear blank cells

WARNING: ANY USE OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this macro code "as is" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

In a macro sheet, enter the following:


      A1: ClearCells
      A2: =ERROR(FALSE)
      A3: =ECHO(FALSE)
      A4: =SELECT.LAST.CELL()
      A5: =ROW(ACTIVE.CELL())
      A6: =IF(ISBLANK(ACTIVE.CELL()),CLEAR(1))
      A7: =IF(AND(ROW(ACTIVE.CELL())=1,COLUMN(ACTIVE.CELL())=1),
           RETURN(),IF(ROW(ACTIVE.CELL())=1,SELECT(OFFSET(ACTIVE.CELL(),
           A5-1,-1)),SELECT(OFFSET(ACTIVE.CELL(),-1,0))))
      A8: =GOTO(A6)

Select cell A1 and choose Define Name from the Formula menu. Select the Command button and choose OK.

Activate the worksheet. Choose Run from the Macro menu and run the ClearCells macro.

After the macro has finished running, save the worksheet, close it, and then reopen it. Your last cell (the cell you get when you press CTRL+END) should be the last cell that contains data.

CAUTION: This macro will delete ALL cell contents (formats, notes, and defined names) that do not have a physical entry within the cell.

NOTE: The ERROR(FALSE) and ECHO(FALSE) functions in cells A2:A3 are not required for this macro to run correctly; however, these functions do significantly increase performance.

To create an example to try the above procedures, do the following:

Open a new Sheet.

Create cell entries from A1 to F15.

In cell H25, type a number and then clear the value from that cell.

From the Formula menu, choose Select Special. When you select the Last Cell option and choose OK button, cell H25 is selected.

If you select row number 25 and choose Delete from the Edit menu, and select column H and choose Delete from the Edit menu. The cell address remains H25 until you select a cell in the A1 to F15 range and save the file.

NOTE: If you are using Microsoft Excel version 3.0 or earlier, H25 will remain the last cell address until you close the file and reopen it. In versions 4.0a and 5.0, you can Clear All and then Save; it is not necessary to reopen the file.

REFERENCES

"Function Reference," version 4.0, pages 57, 122, 135, 364
"Function Reference," version 3.0, pages 33, 62, 68, 201

Additional query words: 3.00 4.00 4.00a erase pointer HOWTO

Keywords : kberrmsg
Version : 2.x 3.x 4.x 5.00 5.00c 7.00
Platform : WINDOWS
Issue type :