Excel UsedRange as Column only address

Question

I am working on an inherited workbook (.xlsb not sure is that matters here or not) that is very large for the small amount of data/reports in it. I discovered that the UsedRange for two of the worksheets is a column only reference: $A:$AU. Hence, 1M+ rows being stored with no data.

I have tried numerous examples on how to "reset" the UsedRange, but none of have worked. There are now Named Ranges or Formulas that have entire column references, no filters to the entire column and no formatting.

Even when I deleted columns A:AU but the UsedRange remains a columns only reference.

Any suggestions would be greatly appreciated.

Answer 1

Answering my own question. I did some additional searching and discovered this add-in: http://xsformatcleaner.codeplex.com/ File size is now less than half of before.

I did get a debug error on opening due it trying to create/access command bars which no longer exist in newer Excel versions but was able to view and use the code and run the Sub ClearExcessRowsAndColumns() on its own and it worked perfectly. Still working through the code to see exactly what it did in the workbook.