OUR THOUGHTS ON:

How to reset the used cell range in MS Excel

Technology

By Kathryn Foradori

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

  1. Press Alt + F11 on your keyboard to launch the Visual Basic Editor.
  2. From the View menu, select Immediate Window to display the area for ad-hoc programming.
  3. 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.
  4. 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:

XSFormatCleaner.xla

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
.

 

You’ve heard our thoughts… We’d like to hear yours

The Schneider Downs Our Thoughts On blog exists to create a dialogue on issues that are important to organizations and individuals. While we enjoy sharing our ideas and insights, we’re especially interested in what you may have to say. If you have a question or a comment about this article – or any article from the Our Thoughts On blog – we hope you’ll share it with us. After all, a dialogue is an exchange of ideas, and we’d like to hear from you. Email us at contactSD@schneiderdowns.com.

Material discussed is meant for informational purposes only, and it is not to be construed as investment, tax, or legal advice. Please note that individual situations can vary. Therefore, this information should be relied upon when coordinated with individual professional advice.

© 2018 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.

comments