3. VBA Tips - Reset the Used Range

Nick's picture

An annoying feature of Excel is not resetting the the used range. What is the used range ? - well, it's meant to be a square around all populated cells.

- How can you select the Used Range for this example ?


Step1: Press CTRL + END - This will select the last populated cell

Step2: - Now press CTRL + SHIFT + HOME


... all your data is now selected. Now, here's the annoying thing. Suppose we delete the last 4 rows, and press CTRL + END again. - the last cell that was populated (but isn't now) is selected.


To reset the used range, we need to resort to using VBA.



If this code is in the worksheet's macro module, and is run, pressing CTRL + END will now go to the correct cell.

Download workbook with code on how to reset the used range..

Check out this tip on how to view the worksheet's code if you are new to VBA

Training Video on how to reset the used range:

Almir's picture

Another way without VBA

When you empty all cells in last rows, just delete those rows and save file. After that CTRL+END will go to the correct cell.