|
Clean Used Ranges
Excel’s Used Range and Last Cell:
|
Check the Last Cell using Ctrl-End.
Too large a Used Range can slow down calculation and increase memory usage and file size
|
Excel only tracks information for the range of cells on a worksheet that it considers as used. This is known as the “Used Range” and is the rectangular area of cells with the “last cell” at the bottom right.
You can select the last visible used cell on a worksheet by using Ctrl-End, or EditGotoSpecialLast cell. Excel considers a cell used if it has been formatted in any way, or contains a constant or a formula or a blank.
When you delete cells Excel does not immediately reset the Last Cell or shrink the Used Range.
|
FastExcel gives you three different methods of cleaning the used range:
.png)
Reset Used Range
This method resets the used range for all worksheets. No information is removed from the workbook, so this is the safest available method.
Clean Excess Used Range
|
Clean Excess used Range is the recommended method.
|
This method removes excess formatting beyond the last cell containing data or a formula. The method used does not cause alterations to formula which reference cells that are cleaned, so this is a safe method to use. The method will handle a large number of special, “difficult-to-clean” problem used ranges. Cleaning complex used ranges with hidden rows and columns can be slow.
Unfortunately this method does not work effectively with Excel 2007 and later versions, and is disabled for these versions.
|
Delete Excess Used range
This method deletes all rows and columns beyond the last cell containing data or a formula.
|
Be careful if you use Delete Excess
|
This action will adjust all formulas that refer to these cells and if the entire range referenced has been deleted the formula is replaced with #N/A:
|
For Example:
Formulas before delete excess used range:
=SUM(Z1:Z1000)
=SUM(A500:A600)
Rows 500 to 1000 are deleted by Delete Excess Used range
Formulas after Delete Excess Used range:
=SUM(Z1:Z499)
=#N/A
Do Not Clean
Allows you to use the other Clean Workbook options without cleaning the used range.
Max Number of Cells per Clean Step
Because deleting rows and columns can be a very memory and resource intensive operation FastExcel does it in small steps. This option allows you to control the how many million cells will be cleaned in each step.
The default is 32 (million cells). The maximum allowable is 64 (million cells) and the minimum allowable is 1 (million cells).
If Excel experiences memory or resource problems during a used-range clean you should retry using a smaller number.
Buffer Rows and Columns
.png)
You can set the number of buffer rows and columns to reserve some rows and/or columns beyond the last cell containing data or a formula.
Use this when you want to:
Keep some additional formatted rows or columns
Prevent references in formulas from being shrunk too much
|
