3. VBA Tips - Reset the Used Range
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:
- Nick's blog
- Login or register to post comments
- 73997 reads
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.