How Do I Quickly Get Rid of Unused Range with VBA?

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?

  1. Identify the unused range by using the Ctrl + Shift + End key combination
  2. Delete the affected rows
  3. Save the workbook

I_hear_yaUsing 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 😉

Sub GetRidOfUnUsedRange()
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
With ActiveSheet
'reaches the last populated cell and goes to the next row
Range("A2").End(xlDown).Offset(1, 0).Select
'uses the curently active cell and goes to the last one of the range
Range(ActiveCell, ActiveCell.SpecialCells(xlLastCell)).EntireRow.Delete
End With
Next
ActiveWorkbook.Save
End Sub

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

Happy Excelling!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.