Resetting the Last Cell in an Excel Worksheet

Apr 22nd 2013
Share this content
Spreadsheets and graphs on a desk
xfgiro/istock

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.

Replies (21)

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)
Replying to John Stokdyk:
avatar
By Gail Perry
Jun 26th 2015 01:11

Thanks, Bob - we've fixed the images.

Thanks (0)
Replying to John Stokdyk:
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)
Replying to Ashok:
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)
Replying to Ashok:
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 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)
Replying to maincpa77:
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)
Replying to maincpa77:
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 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 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 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 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 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 Marvo
Nov 14th 2017 16:44

I didn't need to do that.

Selected row under my last entered data, used "ctrl+shift+arrow down" to select all lines. Deleted selected rows. Saved workbook. On reopening problem solved.

Thanks (0)
avatar
By John_Urban
May 21st 2020 14:14

I tried the ActiveWindow method with the Immediate screen and got a run-time error '438'. I noticed that other people had issues when they tried to do this on sheets with existing VBA. That may be my problem as well. Just thought I would share.

Thanks (0)