By David Ringstrom, CPA
It's frustrating when Excel acts as if the active area of a worksheet is significantly larger than the actual area where you have data. Suddenly your scroll bars move you into uncharted areas, such as column TX or row 5,000. In programming parlance, this is known as the “used range” of a worksheet. Fortunately, there's a quick and simple way to reset the used range when necessary.
As with many aspects in Excel, the used range is fraught with nuance, but you can easily set up an example to understand the concept. As illustrated in Figure 1, carry out these steps in a blank worksheet:
- Type the number 100 in cell A10.
- Press Ctrl-Home to move your cursor back to cell A1.
- Press End-Home to move to the last used cell in your worksheet. Your cursor should now return to cell A10.
- Press Ctrl-G (or the F5 key) to launch the Go To dialog box, enter the address TX5000, and then press Enter.
- Type the number 100 in cell TX5000 and then press Enter.
- Move your cursor up one cell and then press the Delete key to clear cell TX5000.
- Press Ctrl-Home to move your cursor back to cell A1.
- Press End-Home to move to the last used cell in your worksheet. Your cursor will now return to cell TX5000, even though you erased it.
Figure 1: This example illustrates the concept of the “used range” of a workbook.
No amount of deleting rows or columns will reset this used range of your worksheet, but you can use a one-line macro to resolve the issue:
- Press Alt-F11 to launch the Visual Basic Editor.
- Choose View, and then Immediate to display the Immediate window. This task pane allows you to carry out an ad hoc programming task without actually creating a formal macro.
- Type ActiveSheet.UsedRange in the Immediate Window and then press Enter. You won't get any visual indication that anything has happened, but the active area of your worksheet will be reset.
- Choose File and then Close and Return to Microsoft Excel to close the Visual Basic Editor. There's no need to erase the Immediate Window – it will clear itself when you close Excel.
- In your worksheet, press Ctrl-Home to return to cell A1 and then press End-Home to move to the last used cell. Your cursor should now return to cell A10.
Figure 2: Use the Visual Basic Editor to reset the used range of your worksheet.
A previous version of this article first appeared on www.accountingweb.com .
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
