How to check and reset the used range in your model

Using the productivity macro tools to check and reset the used range and end cell.

Download the sample file for this section.

To check the active range, activate the modelling utilities:

Ctrl+shift+6, followed by R 

The macro will generate a new file that lists all the ranges that Excel thinks are being used.

You can see from this report that there are 131 used columns in most of the calculation sheets and that they are ending in column EA. The exception is the FS Qtr sheet which Excel thinks is 16,384 columns wide.

Looking at this sheet, it appears the same as all the others; everything beyond column EA is hidden:

If we unhide the columns, we can see that there is content in there:

We want to quickly clear out all the content and formatting from these cells and rehide the columns.

The Productivity pack macro to reset the end cell can help here:

Firstly select a cell anywhere in the last used column. In our case, this is Column EA. Activate the resent end cell macro:

Ctrl+alt+c

The macro will confirm that you want to clear everything from EB onwards.

Note: if your active cell is anywhere on the time series range in your model, the macro will reset the end column. If your active cell is anywhere to the left of the time series range in your model, the macro will reset the end row.

Once you confirm, the macro will clear all the data and formatting and rehide the columns.

After saving the file, you can confirm that Excel has reset what it considers to be the active range by checking again with the Utilities (Ctrl+shift+6, followed by R).

Excel now thinks that Column EA is now the last used column.

Note: it's sometimes necessary to close the file and reopen it to see the changes to the active range.

Comments

Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.

Subscribe to Financial Modelling Handbook

Don’t miss out on the latest financial modelling guides. Sign up now to get access to the library of members-only guides.
jamie@example.com
Subscribe