CODEWITHSUNDEEP
vbaexcel
Lucas Morin
Lucas Morin

Reputation: 398

Clear cells out of the used range

In one of my macros I loop trough used range, but the used range is way bigger than the actual used range, therefore the macro is impossible to run. I think that cells outside the actual used range may have other impacts on my code such as error "too many cell format" with only one cell.

How can I clean cells outside my used range so that they are not taken into account in my Used range ?

Upvotes: 0

Views: 10759

Answers (2)

Steph Locke
Steph Locke

Reputation: 6146

You can delete everything outside of your actual used range.

You can either do this manually with selecting a row and ctrl+shift+down (and ctrl+shift+right with a column selected) or you can do it programatically by identifying where your data actually ends (either via a COUNTA or xlRight style of command) and then delete everything after those points.

The manual way is good for if it's just a one one off cleanup operation, and if you're doing it in the macro then you may as well just use the method for determining the range to keep to go straight to selecting it, and not bothering to delete outside of the range.

Upvotes: 2

markblandford
markblandford

Reputation: 3193

The Used Range not truly reflecting what is the actual used range is a common issue and just requires some basic cleaning up of the sheet.

Check out this on the Microsoft Support site: http://support.microsoft.com/kb/244435

Upvotes: 2

Related Questions