Have you ever been frustrated when Excel acts as if the active area of your spreadsheet is significantly larger than the set of rows and columns where you currently have data? Your scroll bars span screens and screens of used cells; maybe your file size is overly large, you’re printing extra pages, or you get “Out of Memory” error messages. No amount of deleting rows and columns will set these issues right, but there is a way to reset the coordinates of the last cell used.
The Manual Approach: A Single-Line Macro
- Press Alt + F11 on your keyboard to launch the Visual Basic Editor.
- From the View menu, select Immediate Window to display the area for ad-hoc programming.
- Type “ActiveSheet.UsedRange” (without the quotation marks) in the Immediate Window, then press Enter. There is no visual indication that anything has happened, but the active area of your worksheet will be reset.
- Close the Visual Basic Editor. There is no need to save the macro.
The Installed Approach: An Excel Add-In
There is an Excel add-in available that removes excess formatting and resets the location of the last cell used. To download this add-in, visit the following website:
You’ll need locate and activate the add-in in your Excel Options menu. Once this is done, you will have an additional “Add-In” ribbon with the option to Clear Excess Formats in <Workbook Name>. Clicking this task will have the same results as the one-line macro detailed above.
For more detailed instructions on installing and activating this add-in, check out this Microsoft Knowledge Base Article.
© 2013 Schneider Downs. All rights-reserved. All content on this site is property of Schneider Downs unless otherwise noted and should not be used without written permission.
This advice is not intended or written to be used for, and it cannot be used for, the purpose of avoiding any federal tax penalties that may be imposed, or for promoting, marketing or recommending to another person, any tax related matter.