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.

Editor's Picks