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 😉

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.