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.


Option Explicit
Private lStartTime As Long
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub test()

Dim LR As Long
Dim lCycles As Long
Dim bLog As Boolean

'bLog = True

If bLog Then
Cells.Clear
End If

StartSW
LR = GetLastDataRow("C:\ExcelFiles\", "Lottery.xls", "Sheet1", _
, 23, , , lCycles, bLog)
StopSW , "last data row: " & LR & ", " & "found with " & lCycles & "
cycles"

End Sub

Function GetLastDataRow(strPath As String, _
strFile As String, _
strSheet As String, _
Optional lMinColumn As Long = 1, _
Optional lMaxColumn As Long = 256, _
Optional lMinRow As Long = 1, _
Optional lMaxRow As Long = 65536, _
Optional lCycles As Long, _
Optional bLogToSheet As Boolean) As Long

Dim lOldMinRow As Long
Dim lOldMaxRow As Long
Dim strArgStart As String
Dim strArg As String
Dim bPreviousFound As Boolean

On Error GoTo ERROROUT

If Right$(strPath, 1) < "\" Then
strPath = strPath & "\"
End If

If bFileExists(strPath & strFile) = False Then
GetLastDataRow = -1
Exit Function
End If

'first check if very last row has data to do an early exit
'---------------------------------------------------------
strArgStart = "COUNTA('" & strPath & "[" & strFile & "]" & strSheet & "'!"
strArg = strArgStart & _
"R" & lMaxRow & "C" & lMinColumn & _
":R" & lMaxRow & "C" & lMaxColumn & ")"

If ExecuteExcel4Macro(strArg) 0 Then
GetLastDataRow = lMaxRow
Exit Function
End If

lMaxRow = lMaxRow - 1 'as this was checked above
lOldMinRow = lMinRow
lOldMaxRow = lMaxRow

Do While lMaxRow lMinRow

strArg = strArgStart & _
"R" & lMinRow & "C" & lMinColumn & _
":R" & lMaxRow & "C" & lMaxColumn & ")"

If bLogToSheet Then
'for testing
'-----------
Cells(lCycles + 1, 1) = lMinRow
Cells(lCycles + 1, 2) = lMaxRow
Cells(lCycles + 1, 3) = lOldMinRow
Cells(lCycles + 1, 4) = lOldMaxRow
Cells(lCycles + 1, 6) = lCycles
End If

If ExecuteExcel4Macro(strArg) 0 Then
If bLogToSheet Then
Cells(lCycles + 1, 5) = "found" 'for testing
End If
lOldMinRow = lMinRow
lMinRow = (lMaxRow + lMinRow) \ 2
If lMinRow = lOldMinRow Then
GetLastDataRow = lMinRow
Exit Function
End If
bPreviousFound = True
Else 'If ExecuteExcel4Macro(strArg) 0
If bLogToSheet Then
Cells(lCycles + 1, 5) = "nil found" 'for testing
End If
If lCycles = 0 Then
'nil found in whole range, so return zero
'----------------------------------------
Exit Function
Else
If bPreviousFound = False Then
lOldMinRow = lMinRow
lMinRow = lMaxRow
lMaxRow = lOldMaxRow
Else
lOldMaxRow = lMaxRow
lMaxRow = lMinRow
lMinRow = lOldMinRow
End If
End If
bPreviousFound = False
End If 'If ExecuteExcel4Macro(strArg) 0
lCycles = lCycles + 1
Loop

GetLastDataRow = lMinRow

Exit Function
ERROROUT:

GetLastDataRow = -2

End Function

Function bFileExists(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Function StopSW(Optional bMsgBox As Boolean = True, _
Optional vMessage As Variant, _
Optional lMinimumTimeToShow As Long = -1) As Variant

Dim lTime As Long

lTime = timeGetTime() - lStartTime

If lTime lMinimumTimeToShow Then
If IsMissing(vMessage) Then
StopSW = lTime
Else
StopSW = lTime & " - " & vMessage
End If
End If

If bMsgBox Then
If lTime lMinimumTimeToShow Then
MsgBox "Done in " & lTime & " msecs", , vMessage
End If
End If

End Function


RBS



"Geoff K" wrote in message
...
Hi

Thank you. The method is interesting but very slow when operating on
closed
wbooks.

First I tested it on the bloated UsedRange wbook (AF50918 v S98) - closed.
Out of curiosity I waited more than 10 minutes and gave up.
I then ran it with the wbook open - it took 0.04 seconds to return the
correct last row of 98.

Next, I ran it on another misaligned UsedRange wbook, Q1532 against real
last cell of P153.
Closed, this took 86 seconds. Opened, it took 0.01 seconds

In execution the longest step was in the line If
ExecuteExcel4Macro(strArg)
0 Then within the Do While Loop.
Stepping through with the bloated wbook closed, the code never moved past
the line.

So the original question remains, how can I get MATCH to return a row
number
from both numeric and text fields?
And now this supplementary one - why does MATCH, COUNTA and this method
fail
on the bloated wbook but then processes correctly if I open it.

Ah, I see you have sent another post. Many thanks but it is 02:01 here
and
I will test in the morning.

Geoff

"RB Smissaert" wrote:

Try this code:


Sub test()

MsgBox GetXLRows("C:\ExcelFiles\", "Test.xls", "Sheet1")

End Sub

Also bear in mind that often you know that the only column to consider is
column 1, so in that case you can do, as in my example:

LR = GetLastDataRow("C:\ExcelFiles\", "Lottery.xls", "Sheet1", _
, 1, , , lCycles, bLog)

Making it a lot faster.


RBS


"RB Smissaert" wrote in message
...
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.


Option Explicit
Private lStartTime As Long
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub test()

Dim LR As Long
Dim lCycles As Long
Dim bLog As Boolean

'bLog = True

If bLog Then
Cells.Clear
End If

StartSW
LR = GetLastDataRow("C:\ExcelFiles\", "Lottery.xls", "Sheet1", _
, 23, , , lCycles, bLog)
StopSW , "last data row: " & LR & ", " & "found with " & lCycles & "
cycles"

End Sub

Function GetLastDataRow(strPath As String, _
strFile As String, _
strSheet As String, _
Optional lMinColumn As Long = 1, _
Optional lMaxColumn As Long = 256, _
Optional lMinRow As Long = 1, _
Optional lMaxRow As Long = 65536, _
Optional lCycles As Long, _
Optional bLogToSheet As Boolean) As Long

Dim lOldMinRow As Long
Dim lOldMaxRow As Long
Dim strArgStart As String
Dim strArg As String
Dim bPreviousFound As Boolean

On Error GoTo ERROROUT

If Right$(strPath, 1) < "\" Then
strPath = strPath & "\"
End If

If bFileExists(strPath & strFile) = False Then
GetLastDataRow = -1
Exit Function
End If

'first check if very last row has data to do an early exit
'---------------------------------------------------------
strArgStart = "COUNTA('" & strPath & "[" & strFile & "]" & strSheet &
"'!"
strArg = strArgStart & _
"R" & lMaxRow & "C" & lMinColumn & _
":R" & lMaxRow & "C" & lMaxColumn & ")"

If ExecuteExcel4Macro(strArg) 0 Then
GetLastDataRow = lMaxRow
Exit Function
End If

lMaxRow = lMaxRow - 1 'as this was checked above
lOldMinRow = lMinRow
lOldMaxRow = lMaxRow

Do While lMaxRow lMinRow

strArg = strArgStart & _
"R" & lMinRow & "C" & lMinColumn & _
":R" & lMaxRow & "C" & lMaxColumn & ")"

If bLogToSheet Then
'for testing
'-----------
Cells(lCycles + 1, 1) = lMinRow
Cells(lCycles + 1, 2) = lMaxRow
Cells(lCycles + 1, 3) = lOldMinRow
Cells(lCycles + 1, 4) = lOldMaxRow
Cells(lCycles + 1, 6) = lCycles
End If

If ExecuteExcel4Macro(strArg) 0 Then
If bLogToSheet Then
Cells(lCycles + 1, 5) = "found" 'for testing
End If
lOldMinRow = lMinRow
lMinRow = (lMaxRow + lMinRow) \ 2
If lMinRow = lOldMinRow Then
GetLastDataRow = lMinRow
Exit Function
End If
bPreviousFound = True
Else 'If ExecuteExcel4Macro(strArg) 0
If bLogToSheet Then
Cells(lCycles + 1, 5) = "nil found" 'for testing
End If
If lCycles = 0 Then
'nil found in whole range, so return zero
'----------------------------------------
Exit Function
Else
If bPreviousFound = False Then
lOldMinRow = lMinRow
lMinRow = lMaxRow
lMaxRow = lOldMaxRow
Else
lOldMaxRow = lMaxRow
lMaxRow = lMinRow
lMinRow = lOldMinRow
End If
End If
bPreviousFound = False
End If 'If ExecuteExcel4Macro(strArg) 0
lCycles = lCycles + 1
Loop

GetLastDataRow = lMinRow

Exit Function
ERROROUT:

GetLastDataRow = -2

End Function

Function bFileExists(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Function StopSW(Optional bMsgBox As Boolean = True, _
Optional vMessage As Variant, _
Optional lMinimumTimeToShow As Long = -1) As Variant

Dim lTime As Long

lTime = timeGetTime() - lStartTime

If lTime lMinimumTimeToShow Then
If IsMissing(vMessage) Then
StopSW = lTime
Else
StopSW = lTime & " - " & vMessage
End If
End If

If bMsgBox Then
If lTime lMinimumTimeToShow Then
MsgBox "Done in " & lTime & " msecs", , vMessage
End If
End If

End Function


RBS



"Geoff K" wrote in message
...
Hi

Thank you. The method is interesting but very slow when operating on
closed
wbooks.

First I tested it on the bloated UsedRange wbook (AF50918 v S98) -
closed.
Out of curiosity I waited more than 10 minutes and gave up.
I then ran it with the wbook open - it took 0.04 seconds to return the
correct last row of 98.

Next, I ran it on another misaligned UsedRange wbook, Q1532 against real
last cell of P153.
Closed, this took 86 seconds. Opened, it took 0.01 seconds

In execution the longest step was in the line If
ExecuteExcel4Macro(strArg)
0 Then within the Do While Loop.
Stepping through with the bloated wbook closed, the code never moved past
the line.

So the original question remains, how can I get MATCH to return a row
number
from both numeric and text fields?
And now this supplementary one - why does MATCH, COUNTA and this method
fail
on the bloated wbook but then processes correctly if I open it.

Ah, I see you have sent another post. Many thanks but it is 02:01 here
and
I will test in the morning.

Geoff

"RB Smissaert" wrote:

Try this code:


Sub test()

MsgBox GetXLRows("C:\ExcelFiles\", "Test.xls", "Sheet1")

End Sub

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.

From phone:
I take it you are on XL 2003?
I can't reproduce these very slow results.
If you log, does the logic look OK,
so does its steadily and in logic manner
make progress? Did you try the same
on an ADO recordset? Should be a lot
faster.

RBS

On 3 Oct, 11:31, Geoff K wrote:
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.

With ADO it would work along the code
below and will normally be a lot faster
than with Excel4:


Sub TEST3()

Dim LR As Long

StartSW
LR = GetSheetLastDataRow("C:\ExcelFiles\TestLastRow2003 .xls",
"Sheet1")
StopSW , "last data row: " & LR & ", done with ADO"

End Sub

Function GetSheetLastDataRow(strWB As String, _
strSheet As String, _
Optional lColumn As Long = -1) As Long

Dim rs As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim arr
Dim LR As Long

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strWB & ";" & _
"Extended Properties=Excel 8.0;"

strSQL = "SELECT * FROM [" & strSheet & "$]"

Set rs = New ADODB.Recordset

rs.Open strSQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
arr = rs.GetRows
GetSheetLastDataRow = GetArrayLastDataRow(arr, lColumn)

End Function

Function GetArrayLastDataRow(arr As Variant, Optional lColumn As Long
= -1) As Long

Dim r As Long
Dim c As Long
Dim LR As Long
Dim UB As Long
Dim UB2 As Long
Dim LB As Long
Dim LB2 As Long

UB = UBound(arr)
UB2 = UBound(arr, 2)
LB = LBound(arr)
LB2 = LBound(arr, 2)
GetArrayLastDataRow = LB

If lColumn = -1 Then
For c = LB2 To UB2
For r = UB To GetArrayLastDataRow Step -1
If Not IsEmpty(arr(r, c)) Then
If r GetArrayLastDataRow Then
GetArrayLastDataRow = r
End If
Exit For
End If
Next r
Next c
Else
For r = UB To GetArrayLastDataRow Step -1
If Not IsEmpty(arr(r, lColumn)) Then
If r GetArrayLastDataRow Then
GetArrayLastDataRow = r
End If
Exit For
End If
Next r
End If

End Function


RBS



On 3 Oct, 16:45, Bart Smissaert wrote:
From phone:
I take it you are on XL 2003?
I can't reproduce these very slow results.
If you log, does the logic look OK,
so does its steadily and in logic manner
make progress? Did you try the same
on an ADO recordset? Should be a lot
faster.

RBS

On 3 Oct, 11:31, Geoff K wrote:



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.

Hi Bart

Unfortunately this is not working correctly. It returns a row number much
less than the actual last row.

However the bloated UsedRange wbk does NOT go into an infinite loop. I am
greatly encouraged by this development.

I will test further tomorrow. Many thanks for your continued interest.

Geoff

"Bart Smissaert" wrote:

With ADO it would work along the code
below and will normally be a lot faster
than with Excel4:

That code wasn't tested and indeed it is no good at all, mainly because I
didn't consider the fact
that an array produced by rs.GetArray is transposed.
Shortly after I posted better code (via a phone), but it didn't come
through.
Try this code instead:

Sub test3()

Dim LR As Long

StartSW
LR = GetSheetLastDataRow("C:\ExcelFiles\TestLastRow2003 .xls", "Sheet1")
StopSW , "last data row: " & LR & ", done with ADO"

End Sub

Function GetSheetLastDataRow(strWB As String, _
strSheet As String, _
Optional lColumn As Long = -1) As Long

Dim rs As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim arr
Dim LR As Long

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strWB & ";" & _
"Extended Properties=Excel 8.0;"

strSQL = "SELECT * FROM [" & strSheet & "$]"

Set rs = New ADODB.Recordset

rs.Open strSQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
arr = rs.GetRows
GetSheetLastDataRow = GetArrayLastDataRow(arr, lColumn) + 1 'add one as
0-based array

End Function

Function GetArrayLastDataRow(arr As Variant, Optional lColumn As Long = -1)
As Long

'note that the passed array is transposed as it is produced by rs.GetRows
'------------------------------------------------------------------------
Dim r As Long
Dim c As Long
Dim LR As Long
Dim UB As Long
Dim UB2 As Long
Dim LB As Long
Dim LB2 As Long

'note the bounds are reversed due to the supplied array being transposed
'-----------------------------------------------------------------------
UB = UBound(arr, 2)
UB2 = UBound(arr)
LB = LBound(arr, 2)
LB2 = LBound(arr)
GetArrayLastDataRow = LB

'as sheet columns are 1-based, but this array is 0-based
'-------------------------------------------------------
If lColumn 0 Then
lColumn = lColumn - 1
End If

If lColumn = -1 Then
For c = LB2 To UB2
For r = UB To GetArrayLastDataRow Step -1
If IsNull(arr(c, r)) = False Then
If r GetArrayLastDataRow Then
GetArrayLastDataRow = r
End If
Exit For
End If
Next r
Next c
Else
For r = UB To GetArrayLastDataRow Step -1
If IsNull(arr(lColumn, r)) = False Then
If r GetArrayLastDataRow Then
GetArrayLastDataRow = r
End If
Exit For
End If
Next r
End If

End Function


Note here that the final row result is the table row, so the field row is
zero and the first row is row 1.
This means that it is not the same as the sheet row. It works fine with me
and is reasonably quick.


RBS


"Geoff K" wrote in message
...
Hi Bart

Unfortunately this is not working correctly. It returns a row number much
less than the actual last row.

However the bloated UsedRange wbk does NOT go into an infinite loop. I am
greatly encouraged by this development.

I will test further tomorrow. Many thanks for your continued interest.

Geoff

"Bart Smissaert" wrote:

With ADO it would work along the code
below and will normally be a lot faster
than with Excel4:

Hi

I was just about to post the same thing when I spotted your reply.

It was easy enough to transpose and add 1 for the zero base.

However the ADO function returns me once more to the start position of
mislaigned UsedRanges. On the bloated wbk it returned the last row as 50918
and not the real 98.

I have been here before.

MichDenis in another post some way back now supplied a link
http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid the
pitfalls of flawed UsedRanges but is slow.

This is frustrating because the incidence of flawed UsedRanges is only about
2 wbks in 500. But because of the risk, I have to use the slow method on
every wbook. It would be great if I could detect a flawed UsedRange and run
the 2 recordset method on that wbk only. On the rest of the wbks I could use
SELECT COUNT(*) etc.

FWIW I don't believe SELECT COUNT(*) does any counting at all because it is
so blisteringly quick. I think instead it probably uses the UsedRange last
row or something like it. Unfortunately a null is a record to SQL so if the
wbk has been saved with a flawed UsedRange that is what it uses.

So I am right back to square 1. If only I could detect a flawed UsedRange
in a closed wbk€¦€¦€¦

Geoff


"RB Smissaert" wrote:

That code wasn't tested and indeed it is no good at all, mainly because I
didn't consider the fact
that an array produced by rs.GetArray is transposed.
Shortly after I posted better code (via a phone), but it didn't come
through.
Try this code instead:

As mentioned before, this is also an interesting option to consider:
http://www.rondebruin.nl/copy7.htm
One problem seems to be that it doesn't differentiate between empty cells
and
cells holding the value 0.

RBS



"Geoff K" wrote in message
...
Hi Bart

Unfortunately this is not working correctly. It returns a row number much
less than the actual last row.

However the bloated UsedRange wbk does NOT go into an infinite loop. I am
greatly encouraged by this development.

I will test further tomorrow. Many thanks for your continued interest.

Geoff

"Bart Smissaert" wrote:

With ADO it would work along the code
below and will normally be a lot faster
than with Excel4:

I've been there some time back. AFAIK all it does is download a recordset.

Seems as though I am going to be cursed with this flaw. It's a pity because
my project runs quickly on normal wbks.

Geoff

"RB Smissaert" wrote:

As mentioned before, this is also an interesting option to consider:
http://www.rondebruin.nl/copy7.htm
One problem seems to be that it doesn't differentiate between empty cells
and
cells holding the value 0.

RBS