| Hi Bert,
I've been following this thread, but have been rather busy. I finally got some time to see if I could find an efficient, non-looping method. It seems that formula array is the "magic command" that Paul wondered about.
tell application "Microsoft Excel" set usedAreas to rows of used range of active sheet whose formula array is "" if usedAreas is not missing value then delete range (reverse of usedAreas) end tell
Reversing the usedAreas list is necessary, because Excel deletes rows in the order listed. If not reversed, after one row is deleted, Excel recognizes that the other rows have shifted and will abort with only the first empty row deleted.
Also, if there are no empty rows, then usedAreas is defined as missing value, which must be tested for.
There's another issue. Using this method, all empty rows before the first non-empty row are NOT considered part of the used range and won't be deleted. If you'd like those gone as well, then add the following two lines of code inside the bottom of the tell block.
set firstRow to first row index of used range of active sheet if firstRow > 1 then delete range range ("1:" & (firstRow - 1))
One last observation. In this case, the delete range commands don't need the shift shift up parameter, since entire rows are being deleted and shifting up is implicit.
BTW, this was tested with Excel 2011 (14.3.9) and Excel 2008 (12.3.6).
HTH, Stan C.
|