Excel 2007/2010 Used Range Voodoo – Big Grid used range woes

I have been struggling with cleaning the used range on large workbooks with Excel 2007 and 2010.

Besides the UsedRange.Rows.Count problem discussed in the previous post I have met:

• out of memory
• Excel hangs
• performance problems
• Statusbar not updating

Out of Memory

One of the basic operations in cleaning the used range is to find the real last row and column (the last row and column containing data as opposed to formatting) and then to delete or clear the excess rows and columns beyond the real last row and column. Its better to use clear rather delete because deleting will truncate any references to the rows and columns being deleted, which may cause problems or errors. But sometimes (particularly with Excel 2007/2010) clear does not reset the used range so you may have to use delete.

With the big grid of 2007 and 2010 if the real last row and column are a long way from the used range last row and column you may have to delete a very large range of cells, and guess what: you get an Out of Memory message. So I started to try and find out what sized block of rows or columns would bypass this. But then I hit another problem:

Deleting rows not resetting the used range.

Deleteing the rows was not resetting the used range! And that was not the only problem:

Excel hung on Sheet.UsedRange

After several delete operations when trying to check the used range Excel hung.

Statusbar not updating

Trying to show the progress of the cleaning operation in the statusbar was frustrating: the statusbar message did not update after I set it from VBA.

The solution

After a lot of trial and error(s) I eventually found an instruction sequence that seems to work:

• Use DoEvents before and after each call to Worksheet.UsedRange
• Delete rows before deleting columns
• Check the UsedRange after each clear or delete operation
• Use blocksizes of 4096 rows and 512 64 columns.

The cleaning operation can still be very slow on large complex workbooks, but at least it seems to work!