What is unused range?
The best way to explain it would probably be “a residual redundant space” – Excel simply remembers the last cell in which you ever had data even after that data is long gone.
How do I check if my workbook has unused ranges?
Click on a random cell in your sheet and hit Ctrl + Shift + End
Why do I need to clean up unused ranges?
Unused range adds to your file size! Cleaning it regularly helps optimize enormous files
How do I clean up unused ranges?
- Identify the unused range by using the Ctrl + Shift + End key combination
- Delete the affected rows
- Save the workbook
Using multiple datasets in your workbook?
I know.. It can be quite annoying if you have to repeat the Ctrl+Shift+End operation, followed by a save on more than 1 worksheet tab.
Hence, you can use the VBA snippet below to clear the unused range in each 😉
|Dim Wks As Worksheet|
|Dim i As Integer|
|'written by Angelina Teneva, September 2016|
|For Each Wks In ActiveWorkbook.Worksheets|
|If Wks.Index > 18 Then Wks.Activate|
|'reaches the last populated cell and goes to the next row|
|'uses the curently active cell and goes to the last one of the range|
NB! the code will still execute first on the active sheet before proceeding to tackle the ones that fulfil the “if-condition”, so be mindful of which your active tab is prior running the script