Hi RBS
Thank you for the continued suggestions. I am not familiar with BIFF so
clearly further research is necessary on that and as you can see from the
prelim results the suggested method works ideally on open wbooks but is still
very slow on closed - and that is the essential element in my project.

These prelim results were obtained using the last method and leaving maxcol
at 23. I'm using 2003 SP3.

Wbk 1 - 29 cycles - real last used cell = BV97 UsedRange last cell = same
Closed = 851 ms Open 10ms

Wbk 2 - 27 cycles - real last used cell = W625 UsedRange last cell = same
Closed = 2523ms Open = 10ms

Wbk 3 - 28 cycles - real last used cell = P153 UsedRange last cell = Q1532
Closed = 7020ms Open = 10ms

Wbk 4 - 29 cycles - real last used cell = S98 UsedRange last cell = AF50918
Closed = did not finish Open = 10 ms

As you can see there is a vast difference in results between closed and open
wbooks.

Of significance is the bloated UsedRange wbook (Wbk4) - it did not finish
when closed. The code never moves beyond "If ExecuteExcel4Macro(strArg) 0
Then" in the Do While Loop.

I wonder why because all is perfectly ok with it open.

Geoff


"RB Smissaert" wrote:

This is some further optimized code plus added a timer and logging for
testing.
It works quite fast with me, but this is Excel 2003 and you might be on
2007.
Also bear in mind that you can make it a lot faster if you limit the last
column and you
may know that or you may find that with a procedure with the same principle
or you
could even combine a search for the last row with a search for the last
column.
A really fast way to do this possibly is to work directly on the BIFF Excel
file data and another option
is to capture all the data with ADO into an array and then do a binary
search (similar as in my code)
on that array.