Resetting the Last Cell in an Excel Worksheet

Share this content

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.
Read more articles by David Ringstrom.
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 [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.


Please login or register to join the discussion.

Nice tip. We've added it to our collection of useful and interesting spreadsheet-related articles from the web at

Just a couple of points, though:

1. In one of the steps you say: "Move your cursor up one cell and then press the Delete key to clear cell TX5000." This is only true if the Excel editing option 'After pressing Enter, move selection: Down' has been selected. For many users this step will not work.

2. The images for Figure 1 and Figure 2 are identical. That doesn't seem right.



Thanks, Bob - we've fixed the images.

Thanks for your feedback, Bob, and for adding my article to your curated list. Excel is fraught with nuance, so I appreciate you filling in a gap in my article.

So cool. This has bugged me for a long time.

Thanks, it is a great tip!


Thanks a lot, it helped me so much.

Following your steps I kept getting Runtime Error 424 Object not Found, turns out that there was a "." too many in your code line...
ActiveSheet.UsedRange is the correct input and now it works perfectly. Also, running Office 2013, don't know if that's the reason why I had the trouble.

Thanks for sharing your experience, Willem. I've double-checked my article and I don't see an extra period anywhere in ActiveSheet.UsedRange but perhaps my editor saw your comment and made the fix. Office 2013 isn't the issue, and as you noted, the correct syntax to type in the Immediate window is:


and after you type that press Enter. This technique works in all Windows versions of Excel or Mac for Mac 2011.

If you look at the picture, Figure 2, with the text in the bubble tagged "4", it says Active.Sheet.UsedRange. My sheet must be irredeemable though.

BW, you're exactly right, step 4 in figure 2 is incorrect. I also bobbled the ball in my comment above. In the immediate window its:


and then press Enter. I'll get that figure straightened out. Thanks for pointing that out.

If the above doesn't work, try copying and pasting your "real" area of your worksheet to a new, blank worksheet, which should do the trick as well.

I have ruined a spreadsheet im working on by accidently copying a formula all the way down to row 1,000,000+ I saw this article and hoped it was the answer, unfortunately after attempting the fix a few times, CTRL+END still returns me to the very bottom of the worksheet.

It may be time to transfer your data to a new workbook by selectively copying and pasting your data. Excel workbooks sometimes end up in an irredeemable state.

you can also eliminate the unwanted cells (not just erase) apply David´s tips, and you must be all set also (did happend to me too)

Thanks for a great tip! I wish I had found this much earlier.

I appreciate the feedback! I'm so glad you found my article helpful.

Hi David - Awesome tip. Thank you. It worked great on a blank sheet but didn't seem to work on my sheet that has existing VBA code. Is there an addditional step in that case?

Once in a while I find that it won't work on a particular worksheet. In such cases you might try adding a new worksheet, and then copying and pasting just the actual used area of your spreadsheet to a new workbook. Or you might try the Repair option that appears when you click the little menu button next to the Open button in Excel's Open dialog box. It sounds like your workbook might have some sort of minor underlying data corruption.

Little ones, I had this issue too, just comment all the VBA codes, run the steps from David´s article and you must be ready... uncomment the code and keep working.

Hi David,thank you so much for the help! You saved me many hours of scrolling up and down the worksheet so I have some free time to bake a cake now :)

Mmmm....cake! Thanks for the fun feedback, what a great way to make use of your new-found free time!

This resolved a resources error I was getting trying to insert a column.

Excellent! That is great to know. Thank you for the feedback!

This doesn't occur frequently enough for me to remember the steps, so I came here and found the End-Home keystrokes to travel to the last cell (yea!). But there is also a simple method to clear all unwanted cells from that point back to your "desired" last cell (which I never remember). After a few attempts this worked:

Nav to the last active cell using End-Home > highlight the blank rows back to your desired data > Clear all > then delete the rows > Save. Repeat for blank columns if necessary. Another EndHome command takes me to the visible cell I desired. (These are sheets with no VBA.)

Thanks for contributing to my article! I agree that these steps sometimes work, but the steps in my article relate to more troublesome worksheets where simply clearing and deleting rows won't do the trick.

Thanks, this is quick and useful. Not only saves time saving the file but also the file size.

I'm glad you found this helpful!