I agree that my code is slow, and look forward to trying Tom's suggestion.
I am surprised that saving a file resets the used range as in my experience
on opening a file, "used range" gives the wrong values.

"Tom Ogilvy" wrote:

Are you using excel 97. If not, then I use this all the time and it works.
If it doesn't work for you, then try saving the workbook after you do what I
suggested but not closing and opening it. If that doesn't work, then I
guess you will have to close and open it, but that hasn't been my experience.

Gleam is providing some slow code to discover the rows and columns to be
deleted, but never actually resets the usedrange. I assumed you already knew
that and emphasized the fact that you actually need to delete entire rows and
columns that were unused before resetting the range.

Debra Dalgleish has faster code to do this:
http://www.contextures.com/xlfaqApp.html#Unused

Note that she advocates saving the workbook to reset the range, but as I
said, I haven't found that to be necessary in later versions of excel if you
executed the command
Activesheet.usedrange
after deleting entire rows and columns of unused space.
--
Regards,
Tom Ogilvy


"Posse John" wrote:

For whatever reason, Excel doesn't 'reset' the usedrange until you save and
reopen the workbook...

I'm trying to 'reset' it programatically.