Resetting the Last Cell in an Excel Worksheet

exceltips.jpg

Spreadsheets and graphs on a desk
xfgiro/istock
27

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

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Replies

Please login or register to join the discussion.

avatar
By Bob Watson
Jun 26th 2015 01:11

Nice tip. We've added it to our collection of useful and interesting spreadsheet-related articles from the web at http://www.i-nth.com/resources...

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.

Cheers,

Bob.

Thanks (0)
avatar
By Gail Perry
Jun 26th 2015 01:11

Thanks, Bob - we've fixed the images.

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By tag
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Lilian
Jun 26th 2015 01:11

Thanks, it is a great tip!

Thanks (0)
avatar
By NickT
Jun 26th 2015 01:11

Nice!

Thanks (0)
avatar
By RR
Jun 26th 2015 01:11

Thanks a lot, it helped me so much.

Thanks (0)
avatar
By Willem
Jun 26th 2015 01:11

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 (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

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:

ActiveWindow.UsedRange

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

Thanks (0)
avatar
By BW
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

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:

ActiveSheet.UsedRange

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.

Thanks (0)
avatar
By matt
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By Chesco
Jun 26th 2015 01:12

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 (0)
avatar
By Max
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By littleones
Jun 26th 2015 01:11

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?

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By Chesco
Jun 26th 2015 01:12

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.

Thanks (0)
avatar
By Anita
Jun 26th 2015 01:11

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 :)

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Kevin N Wahl
Jun 26th 2015 01:12

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

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

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

Thanks (0)
avatar
By wesejohnson
Jun 26th 2015 01:12

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 (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

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 (0)
avatar
By Ashok
Jun 26th 2015 01:12

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

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

I'm glad you found this helpful!

Thanks (0)