Software

Use VBA to bring in the range

When you want to locate the last cell containing data in a worksheet, you can press Ctrl + End -- but that may take you beyond the range of data you're using. Here's a trick for resetting the used range so that Excel will take you to the right spot.

If you want to find out where the last cell containing data is located on your worksheet, press Ctrl + End. This will move your cursor to the last cell that contains data, which may be several blank columns away from the cells you actually use. You can try to clear the unused cells from any content or formatting, but you will find that Excel still takes you beyond the used range.

Fortunately, there is a quick way to reset the used range so that Excel no longer includes cells that once had data. Follow these steps:

  1. Open the worksheet you want to reset.
  2. Press Alt + F11.
  3. Press Ctrl + G to display the Immediate window, if necessary.
  4. Click in the Immediate Window and type the following:

ActiveSheet.UsedRange

  1. Press Enter.

  1. Press Alt + Q.

Now when you press Ctrl + End, Excel should take you to the end of the correct range in use.


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips. Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.
7 comments
grahamrice
grahamrice

None of the suggestions - VBA code, copying to another spreadsheet or deleting columns/rows are normally needed! All you have to do is SAVE the Workbook. There is no need to close, just SAVE whilst keeping the workbook open. Then Ctrl/End to move to revised "Last Cell". The SAVE operation AUTOMATICALLY resets the last cell position. Also it does this for EVERY Worksheet in the Workbook - most of the other options only work on the currently "Active" Worksheet. As far as I can see the only time to use any of the other methods is when you do not want to save the current activity. I know that the SAVE method works in Excel 97 and 2000, I assume it has not changed for later versions?

rrberndt
rrberndt

Mary Ann, Thanks for a great tip. Because I had some data beyond the area I was interested in, I had to delete the cells (that were beyond). And you're right, Ctrl+End took me out to where the data used to be. Using your Immediate Window trick - well, it makes Ctrl+End go to the end of the data I want. Excellent! Rich

newsletter
newsletter

As long as you have data in each row from top to bottom in the column your cursor is currently in, use Ctrl + Down Arrow. If there is a cell that is blank this will stop there though, so use a column that you know has data in each row cell. This works for Excel 2003.

jennyh1234
jennyh1234

It didn't work for me. Is this for Excel 2007. I use 2003. I calculate some payroll in small spreadsheets, but there is many repetative parts that I can't automate because I can't depend on Excel to always go to the end of the data.

MentorCtl
MentorCtl

Delete unused Rows and Columns, then save work sheet. Ctrl-End will now take you to last cell in usage.

sdmayhew
sdmayhew

A different way is to copy the data in a spreadsheet and paste it into a new spreadsheet. sometimes this is quicker than trying to delete all columns and rows out of a exisitng spreadsheet

bcrowell
bcrowell

I tried your suggestion but it did not work. After applying the command, the CNTL-HOME still takes me out to Column BM - way out beyond my range. I have found in the past that this occurs when formating is applied to an entire row or column instead of the cells within the intended schedule on the worksheet. Any ideas why it would not have worked for me?

Editor's Picks